Help with Average

[rhani111]

Thanks for reading my post,

I have posted several times and never got an answer before, so here's hoping

I have a column that notes the date in dd/mm/yy format. This column is updated daily and is from B2:B400. The problem is this, on each entry date, a truck's availability in percent is calculated. At the end of the month, I need the average of the total truck availability. The hassle I am having, is getting the formula to look in column B, if the date matches the date in column O, then average the truck's availability in percent form in column Q......god now even I'm confused....lol

Hope this time I can get some help!! I'm a very frustrated aussie excel user tonight...

[Alex Delamain]

Can you clarify a few things - maybe post some sample data (in the psot rather than an attachment). It sounds relatively straightforward if the requirement cna be clarified.

[rhani111]

Okies, I'll give it a go...

Column B Column J Column O Column Q
Date Truck Availability (Daily) Days of Month Average
Daily
Avail.

01/02/05 88.37% 01/02/05 Calculation?????
02/02/05 73.50% 02/02/05
03/02/05 70.90% 03/02/05
07/02/05 100.00% 04/02/05
01/02/05 90.50% 05/02/05
07/02/05 50.00% 06/02/05

I don't know if that makes sense to you...lol..but B runs from B2:B400, likewise Column J.....each DAY the total truck availability is calculated on the time working divided by hours of the day supposed to work.....that gives me the Truck Availability (Daily)...I then have to calculate the mean or average daily availabilty at the end of the month...make sense?????

Thanks very much for getting back to my post!!!!

[Alex Delamain]

Ok here goes! Sorry I have not followed exactly your data layout but I hope this makes sense............

Date Truck Hours Run Theoretical Hours Availability
01/02/2005 A 5 15 33%
01/02/2005 B 12 15 80%
02/02/2005 A 1 15 7%
02/02/2005 B 14 15 93%
03/02/2005 A 0 15 0%

I have named a cell "Start" and another "End" so you can select the period to summarise over - ie formula will summarise all data between "start" and "end" date inclusive.
G9 is the cell you enter the Truck id into.

=SUMPRODUCT((A2:A20>=start)*(A2:A20<=end)*(B2:B20=G9)*(C2:C20))/SUMPRODUCT((A2:A20>=start)*(A2:A20<=end)*(B2:B20=G9)*(D2:D20))

I have also gone back to the number of hours run and theoretical hours available as this gives a more accurate avilability than averaging the daily figures.

Hope this helps

[rhani111]

Thanx very, very much Alex,

Works fantastically!!!! Although we don't track start and finish time, (this is for a mining company and they only want to track the total hours a day vs the total time the vehicle was "down") I managed to tweak the formula you supplied to give me exactly what I need....

So again, THANX!!!!
You guys here ROCK!