average hourly output for date, result shown by day

  • Thread starter Thread starter Morgan
  • Start date Start date
M

Morgan

hello there,

i have a template in excel2007 that is blank until the data will begin to be
entered, in column A i have the date in the format 14/03/01, in column D i
have hours worked (in regular number eg 2.89) and in column M i have output
in units.

i was wondering if there was a formula that could return the average hourly
output and total output in the style below?

Average Hourly Output Total Output
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday

i have tried setting up another column that corresponds to the date column
A, and trying to get the above the long way using the formula
=TEXT(A2,"dddd") but as it is based on empty cells, all the blank cells come
up with 'saturday' or the last value,

any help would be great thank you!
 
Assumesource data as described in Sheet1
Assume the results table that you want is in Sheet2

Calculate Average Hourly Output
Put in B2, array-enter ie press CTRL+SHIFT+ENTER to confirm the formula
=AVERAGE(IF((TEXT(Sheet1!A$2:A$10,"dddd")=A2)*(Sheet1!A$2:A$10<>"")*(Sheet1!D$2:D$10<>""),(Sheet1!D$2:D$10)))
Copy B2 down

Calculate Total Output
Put in C2, normal ENTER:
=SUMPRODUCT(--(TEXT(Sheet1!A$2:A$10,"dddd")=A2),Sheet1!M$2:M$10)
Copy C2 down

Adapt the ranges to suit the actual extents. Verify the expected outputs for
your test source data. Success? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
sorry Max, i couldn't get it to work, the first formula seems to leave out
column M 'output' i just wanted a formula that i could plug into the table
below, for each day and would return the results, thanks for trying though

Average Hourly Output Total Output
 
Back
Top