average hourly output for date, result shown by day
 

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!

--
cheers

average hourly output for date, result shown by day
 

Hi,
use

=if(A2="","",TEXT(A2,"dddd"))

to get the average just do

=M2/D2

"Morgan" wrote:

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!

--
cheers

average hourly output for date, result shown by day
 

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)*(She et1!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,

any help would be great thank you!

--
cheers

average hourly output for date, result shown by day
 

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
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday

--
thanks


"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)*(She et1!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,

any help would be great thank you!

--
cheers

average hourly output for date, result shown by day
 

.. 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

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

--
thanks

average hourly output for date, result shown by day
 

thanks Max


"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

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

--
thanks