Daily Burn Rates

[YANKRAY]

Using Excel 2007.

I have a spreadsheet with Aircraft across the columns.
Down the rows are station where work is performed.

Each aircraft has 3 columns of data.
Estimated Hours to Complete (ETC), Start Date, and Estimated Completions date.

It looks like this;

Aircraft 01 Aircraft 02
ETC Start Pred Fin ETC Start Pred Fin
Station
01 0 08/15/09 09/01/09 20 11/06/09 11/09/09
02 60 11/05/09 11/09/09 80 11/06/09 11/15/09
03 30 11/07/09 11/07/09 30 11/13/09 11/13/09

There are about 15 aircraft across the sheet.

The daily burn is the # of days from start to finish divided by the ETC hours. Unless the start date has passed, then the dail burn is the # of days from today to finish divided byt the ETC hours.

At the end of all the aircraft is a column for each day. The days start from the lowest start date for an aircraft to the latest finish date for an aircraft.

What I am trying to do is for each day show the daily burn rate required for each station.

In the example, for the date 11/06/09 for station 02 I would want to see 23 hours.

Any ideas on how to write the formula for the end of the sheet to determine the daily burn?

Thanks,
Ray

 

[SkipVought]

Hi,

What is your algorithm for calculating 23? Please be very specific!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[YANKRAY]

For the example result of 23.

On Aircraft 1, station 02 started on 5 Nov.

Since that has passed, the start date to use would be the current date of 6 Nov.

There are 4 days between 6 Nov and 9 Nov (the 6th, 7th, 8th and 9th).

The ETC hours of 60 divided by the 4 days is 15 hours per day.

On Aircraft 2, station 02 started on 6 Nov.

There are 10 days between 6 Nov and 15 Nov (the 6th, 7th, 8th, 9th, 10th, 11th, 12th, 13th, 14th, and 15th).

The ETC hours of 80 divided by the 10 days is 8 hours per day.

So for 6 Nov the Daily Burn would be the 15 hours for Aircraft 1 and the 8 hours for Aircraft 2 totaling 23 hours for the day.

 

[SkipVought]

Your data format makes using it VERY DIFFICULT!!! Akin to tying one hand behind your back, going blindfold and shooting yourself in the foot!

Rather...
[tt]
AC Station ETC Start Pred Fin
01 1 0 8/15/2009 9/1/2009
01 2 60 11/5/2009 11/9/2009
01 3 30 11/7/2009 11/7/2009
02 1 20 11/6/2009 11/9/2009
02 2 8 11/6/2009 11/15/2009
02 3 30 11/13/2009 11/13/2009
[/tt]
Ths it's a snap. Here's your formula entered using Named Ranges AND as an ARRAY (ctr+SHIFT+ENTER)...
[tt]
=SUMPRODUCT((Station=G8)*(ETC/(Pred_Fin-IF(Start<TODAY(),TODAY(),Start)+1)))
[/tt]
Where G8 is a Station id.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, for stations 1-3 I get...
[tt]
1 5
2 23
3 60
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}