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,
> 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,
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 ---
"Morgan" wrote: > 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,
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
"Max" wrote: > 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 > --- > "Morgan" wrote: > > 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?
> > 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,
> .. plug into the table below, for each day and would return the results
mm, but that's exactly what was suggested? How about a little working sample to illustrate it: http://cjoint.com/?lfqpzRHQm2 Average n Sum by day.xls -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik ---
"Morgan" wrote: > 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
"Max" wrote: > > .. plug into the table below, for each day and would return the results > mm, but that's exactly what was suggested? > How about a little working sample to illustrate it: > http://cjoint.com/?lfqpzRHQm2 > Average n Sum by day.xls > -- > Max > Singapore > http://savefile.com/projects/236895 > Downloads:27,000 Files:200 Subscribers:70 > xdemechanik > --- > "Morgan" wrote: > > 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
|
