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

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

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

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

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

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