Averaging

[Leslie]

Hi,

I need help with averaging a column of numbers.

I have two columns of numbers. I want to find the average of the
numbers in the first column that correspond to the numbers in the
second column that are greater than 0.

3 -2
4 6
6 9
7 0

So in this case the answer would be 5. The only two numbers in the
first column that correspond to numbers greater than zero in the
second column are the 6 and the 4. The average of 6 and 4 is 5.

Thanks,

Leslie

 

[Dave Peterson]

One way:

=SUMIF(B1:B4,">"&0,A1:A4)/COUNTIF(B1:B4,">"&0)

Or to avoid possible errors:

=IF(COUNTIF(B1:B4,">"&0)=0,"No matches",
SUMIF(B1:B4,">"&0,A1:A4)/COUNTIF(B1:B4,">"&0))

 

[Guest]

Another way, which needs to be confirmed with CTRL+SHIFT+ENTER

=AVERAGE(IF(B1:B4>0,A1:A4))

 

[Leslie]

I tried the first one and it worked fine.

Thank you

Leslie