Averaging

  • Thread starter Thread starter Leslie
  • Start date Start date
L

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
 
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))
 
Another way, which needs to be confirmed with CTRL+SHIFT+ENTER

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