Averages

[Guest]

Hello, I am averaging a column that may contain negative numbers. I need to have the average ignore the negative numbers and only calculate the positive one. How do I do this?

 

[JulieD]

use a sumif / countif
assuming range is a1:a7
formula would be
=SUMIF(A1:A7,">"&0)/COUNTIF(A1:A7,">"&0)

Cheers
JulieD

Hello, I am averaging a column that may contain negative numbers. I need

to have the average ignore the negative numbers and only calculate the
positive one. How do I do this?

 

[Guest]

Thanks Julie. Will that formula also ignore zeros?

 

[JulieD]

hi Dan

the countif function has the following structure:

=countif(range to check for the criteria, criteria)
so in this case
we're looking in A1:A7 for any numbers greater than (>) zero

if you want it to include zeros, change the criteria to ">="&0

the sumif function is very similar to the countif function (has the same
first two arguments, but can have a third as well, which tells excel what
range to add up ... as you wanted to add up the same range that met the
criteria, i omitted it in the example i sent you.

hope this helps

Cheers
JulieD

 

[Bernard Liengme]

This formula can be simplified to =SUMIF(A1:A7,">0")/COUNTIF(A1:A7,">0")
You really need concatenation only when referencing a cell as in
=SUMIF(A1:A7,">"&B1)/COUNTIF(A1:A7,">"&B2)