I need help to make a not-so-easy AVERAGE formula!

[sandres74]

Here's a description of spreadsheet that I am making for the sales of a store:

- Column A has all the dates of the year (January 1, January 2, January 3,
etc.) for the entire year listed one after another (so a total of 365 rows,
starting in row 1).
- Column B has the day of the week (Monday, Tuesday, Wednesday, etc.)
corresponding to the date next to it in column A.
- Column C has the sales amount (example: $125.50) corresponding to the
date/day listed in the same row of columns A and B.

I have the following formula (which works great) which gives me the SUM of
all sales made ONLY on MONDAYS for the entire year to appear in a cell:
=SUMIF(B:B,"MONDAY",C:C)

My question is:
Is there a formula that would allow Excel to calculate the AVERAGE
(excluding blank cells and cells with zeros!) of all sales made ONLY on
MONDAYS for the entire
year? I have tried to modify the formula above to do that, but to no avail.
I know not what I am doing! Can anyone help?

Thanks in advance!

 

[rslaughter5]

Use AVERAGEIFS(C:C,A:A,"Monday",C:C,">0"). If you do not have Excel 2007, use
SUMIF()/COUNTIF() together.

 

[Gary''s Student]

Since you already have the sum, you just need to divide by the count of the
non-zero, non-blank, values:

=SUMIF(B:B,"MONDAY",C:C)/SUMPRODUCT(--(B1:B365="Monday"),--(C1:C365<>0))

 

[Mike H]

Hi,

This depends on PROPERLY formatted dates and to prevent the formula becoming
too long I used cell references for the criteria

D1= 1/1/2009
D2 = 31/12/2009
D3 = Monday
Note D3 is simply text, no formula
now to get the day of the week in column B I used in B1
=Text(A1,"dddd")
and dragged down

Now the array formula. See below on how to enter an array formula
=AVERAGE(IF(A1:A365>=D1,IF(A1:A365<=D2,IF(B1:B365=D3,IF(C1:C365>0,C1:C365)))))



This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.


Mike

 

[Luke M]

While the first formula is good, the second formula for earlier versions
doesn't ignore zero values. Could do:

=SUMIF(B:B,"MONDAY",C:C)/SUMPRODUCT(--(B$1:B$365="MONDAY"),--(C$1:C$365>0))