Average If Question

[Guest]

We are trying to average the numbers in one column only if they equal any or
all of 6 different numbers from a different column. We have a formula that
looks for one number, but we need a formula that looks for all numbers.
=AVERAGE(IF(B2:B1200=7474,C2:C1200,FALSE)) where column B equals a 4 digit
course number and there are six different course numbers that need to be
averaged out of total of 20 different course numbers, so if B = 7474, 7921,
8612, 9011, 3945, 2209 then the numbers in row C will be counted towards the
average.

 

[JulieD]

Hi Renee

does the array formula
=AVERAGE(IF(B1:B9={7474, 7921, 8612, 9011, 3945, 2209},C1:C9,FALSE))
give you what you're after
(enter with control & shift & enter not just enter)

Cheers
JulieD

 

[Peo Sjoblom]

One way

=SUMPRODUCT((B2:B1200={7474,7921,8612,9011,3945,2209})*(C2:C1200))/SUMPRODUCT(--(B2:B1200={7474,7921,8612,9011,3945,2209}))

 

[Guest]

Thanks! That worked for us!

Hi Renee

does the array formula
=AVERAGE(IF(B1:B9={7474, 7921, 8612, 9011, 3945, 2209},C1:C9,FALSE))
give you what you're after
(enter with control & shift & enter not just enter)

Cheers
JulieD

 

[Guest]

Thanks! This works too.

One way

=SUMPRODUCT((B2:B1200={7474,7921,8612,9011,3945,2209})*(C2:C1200))/SUMPRODUCT(--(B2:B1200={7474,7921,8612,9011,3945,2209}))

 

[Guest]

The only problem is that it seems to be including blank cells. Any advice?

 

[Guest]

The only problem is that it seems to be including blank cells. Any advice?