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))))
On Feb 4, 1:12*am, joeu2004 <joeu2...@hotmail.com> wrote:
> On Feb 3, 7:19*pm, Blake <sfc...@gmail.com> wrote:
>
> > 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.
|
Similar Threads
