Average If Question

R

Renee

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.
 
J

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
 
P

Peo Sjoblom

One way

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

Renee

Thanks! That worked for us!

JulieD said:
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
Click to expand...
 
R

Renee

Thanks! This works too.

Peo Sjoblom said:
One way

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