average if question

[Blake]

I have the following formula in a table:

{=AVERAGE(IF(MOD(ROW(E16:E4994)-ROW(E16),
7)=0,IF(E16:E4994>0,E16:E4994)))}

It returns the average of every 7th row in a range in column E. In
column C I have the day of the week. I enter data daily going down.
Is there a way to return the average of every 7th row for Monday's
only? Or Tuesday? Etc.

Thanks

 

[joeu2004]

I have the following formula in a table:
{=AVERAGE(IF(MOD(ROW(E16:E4994)-ROW(E16),
7)=0,IF(E16:E4994>0,E16:E4994)))}
It returns the average of every 7th row in a range in
column E.  In column C I have the day of the week.  I
enter data daily going down. Is there a way to return
the average of every 7th row for Monday's only?

It is unclear what form the "day of the week" is in in column C.

If you have text like "Mon", then try this array formula [*]:

=AVERAGE(IF(C16:C4994="Mon",IF(E16:E4994>0,E16:E4994)))

If you have real dates that you format to show only the day of the
week (Custom ddd), then try this array formula [*]:

=AVERAGE(IF(WEEKDAY(C16:C4994)=2,IF(E16:E4994>0,E16:E4994)))

Caveat: If there is no data for some Monday (e.g. stock market
holiday), the formula will skip a week. It is unclear if that is what
you truly want in that case.

[*] Enter an array formula by pressing ctrl+shift+Enter instead of
just Enter. Excel will display the formula surrounded by curly braces
in the Formula Bar. You cannot type the curly braces yourself. If
you make a mistake, select the cell, press F2 and edit as needed, then
press ctr+shift+Enter.

 

[Blake]

Thanks for your reply. It took a while (kept getting a divisor/0
error), and I had to amend it for an extra condition, but I got the
following formula to fill in my range and it do exactly as I wanted.
Much appreciated.

=AVERAGE(IF(WEEKDAY($C$16:$C$4994)=$Q$6,IF($D$16:$D$4994=$Q7,IF(E$16:E
$4994>0,E$16:E$4994))))

I have the following formula in a table:
{=AVERAGE(IF(MOD(ROW(E16:E4994)-ROW(E16),
7)=0,IF(E16:E4994>0,E16:E4994)))}
It returns the average of every 7th row in a range in
column E.  In column C I have the day of the week.  I
enter data daily going down.  Is there a way to return
the average of every 7th row for Monday's only?

It is unclear what form the "day of the week" is in in column C.

If you have text like "Mon", then try this array formula [*]:

=AVERAGE(IF(C16:C4994="Mon",IF(E16:E4994>0,E16:E4994)))

If you have real dates that you format to show only the day of the
week (Custom ddd), then try this array formula [*]:

=AVERAGE(IF(WEEKDAY(C16:C4994)=2,IF(E16:E4994>0,E16:E4994)))

Caveat:  If there is no data for some Monday (e.g. stock market
holiday), the formula will skip a week.  It is unclear if that is what
you truly want in that case.

[*] Enter an array formula by pressing ctrl+shift+Enter instead of
just Enter.  Excel will display the formula surrounded by curly braces
in the Formula Bar.  You cannot type the curly braces yourself.  If
you make a mistake, select the cell, press F2 and edit as needed, then
press ctr+shift+Enter.