running day-of-week average

[silky_green]

I have a sheet with all the daily totals and I'm trying to calculate th
current average by day-of-week:

DATE DOW TOTAL
01/01/04 4 1000
01/02/04 5 2000
01/03/04 6 2000
01/04/04 7
01/05/04 1 1000
01/06/04 2 1000
01/07/04 3 3000
01/08/04 4 2000
...
12/31/04 5

so if today is 05/30/04, i want to see the average so far of all th
mondays, tuesdays, wednesdays, etc.

i don't know if there an "average" function that can calculate this fo
me. so i thought if I can use the "sumif" function to get the total
for the day-of-week, and then get the count of how many mondays hav
passed, i can get a current average. is there a "count" function tha
will allow me to get how many mondays have totals in them?

is there a simple way to do this without the sumif and counti
function?

thank you

 

[Frank Kabel]

Hi
try the following kínf of array formula (entered with
CTRL+SHIFT+ENTER):
=AVERAGE(IF(WEEKDAY(A1:A100)=2,C1:C100))
for mondays

 

[silky_green]

Thanks Frank, but the sheet contains cells for future dates that have n
value (TOTAL=0). When I tried the formula you suggested, the averag
seems to be calculated for all the Mondays in the entire year, not jus
the Mondays that have passed (or have a TOTAL > 0). Can you help m
out a little further? Thank you.

 

[Frank Kabel]

Hi
try (also an array formula)
=AVERAGE(IF((WEEKDAY(A1:A100)=2)*(C1:C100<>0),C1:C100))

 

[Peo Sjoblom]

You could try

=AVERAGE(IF((WEEKDAY(A1:A100)=2)*(C1:C100<>""),C1:C100))

or if zeros and negative values are non existent

=AVERAGE(IF((WEEKDAY(A1:A100)=2)*(C1:C100>0),C1:C100))

both

entered with ctrl + shift & enter

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom