Average If & If

[Guest]

Hello everyone,
I am trying to average the numbers in a column based on the value of a
referenced cell. The referenced cell has a drop list validation containing
12 months and year to date. If "Year to Date" is selected then the formula
should average the entire column, if a month is chosen the the cell should
just average the numbers within the column that correlate to an adjacent cell
with the month the row was filled in.
What I envisioned is this
..
=IF(A2=IR9,AVERAGE($U$10:$U$250),AVERAGE(IF($IT$10:$IT$250="YES",$U$8:$U$253)))
..
A2 = Drop down validation
IR9:IR20 = Source of validation (IR9 being "Year to Date")
U10:U250 = column to average
IT10:IT250 = Cells that formulates whether A2 = month row was filled in
..
Hope one of you experts can help.
Brgds,
Steve

 

[Dave Peterson]

I changed U8:U253 to match the range in IT (IT10:IT250) and array entered your
formula and it worked nicely:

=IF(A2=IR9,AVERAGE($U$10:$U$250),AVERAGE(IF($IT$10:$IT$250="YES",$U$10:$U$250)))

(ctrl-shift-enter instead of just enter)

 

[Guest]

Dave,
I copied and pasted it from your reply and it worked for me as well. Don't
know why at home worked and not at office.
Thanks much for your help.
SteveT