Averages

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
use a sumif / countif
assuming range is a1:a7
formula would be
=SUMIF(A1:A7,">"&0)/COUNTIF(A1:A7,">"&0)

Cheers
JulieD


Dan D said:
Hello, I am averaging a column that may contain negative numbers. I need
Click to expand...
to have the average ignore the negative numbers and only calculate the
positive one. How do I do this?
 
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
 
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)
 
Back
Top