Average ignore values of 0

  • Thread starter Thread starter MeritageSue
  • Start date Start date
M

MeritageSue

I'm rolling up data within a sheet, and I want it to only
average numbers where there is a value greater than 0.
Currently it is returning an average of all 12 numbers
(including the items with a value of 0---so the average is
the total of the numbers divided by 12). I want it to
only average the cells that have a value greater than 0.
Currently my formula is: =AVERAGE
(T22,T50,T64,T78,T92,T106,T120,T134,T148,T162,T176,T36)

I know there has to be an easy way to do this, but I'm
stumped.
 
I'm rolling up data within a sheet, and I want it to only
average numbers where there is a value greater than 0.
Currently it is returning an average of all 12 numbers
(including the items with a value of 0---so the average is
the total of the numbers divided by 12). I want it to
only average the cells that have a value greater than 0.
Currently my formula is: =AVERAGE
(T22,T50,T64,T78,T92,T106,T120,T134,T148,T162,T176,T36)

I know there has to be an easy way to do this, but I'm
stumped.
Click to expand...

The non-contiguous range makes it more difficult.

One way: AVERAGE ignores most non-numeric values. So for your formulas in the
cells you wish to average, you could substitute the formula:

=IF(YourFormula>0,YourFormula,"")


--ron
 
-----Original Message-----


The non-contiguous range makes it more difficult.

One way: AVERAGE ignores most non-numeric values. So for your formulas in the
cells you wish to average, you could substitute the formula:

=IF(YourFormula>0,YourFormula,"")


--ron
.
Click to expand...
Thanks Ron, I used that formula in the cells I want to
average (having them return a value of "novalue" if they
are less than zero) and it is working fine in those cells,
but it doesn't seem to be making a difference in having
the average ignore those "novalue" cells when it tries to
make the average, it seems to be counting them as zero.

Is there something I can use other than "novalue" that
would cause them to be ignored by the average function?
 
Thanks Ron, I used that formula in the cells I want to
average (having them return a value of "novalue" if they
are less than zero) and it is working fine in those cells,
but it doesn't seem to be making a difference in having
the average ignore those "novalue" cells when it tries to
make the average, it seems to be counting them as zero.

Is there something I can use other than "novalue" that
would cause them to be ignored by the average function?
Click to expand...

Average ignores text, logical values, and empty cells so if there really is
"novalue" in those cells, as the result of an IF statement, the AVERAGE
function should not be counting them.

Perhaps if you post some formulas and data, we can see what the problem is.


--ron
 
You can combine sumif and countif functions:
Let us assume the range of cells with data is t1 to t200.
Now you can first create a function in cell t202 = sumif(t1:t200,
">0",t1:t200)
Then create a function in cell t203 = countif(t1:t200, ">0")
The average of all numbers above 0 will be (t202/t203)
If your intention is to include negative values as well as replace ">0" by
"<>0"

Eechhutti R.Rajasekaran
(e-mail address removed)
 
You can combine sumif and countif functions:
Let us assume the range of cells with data is t1 to t200.
Now you can first create a function in cell t202 = sumif(t1:t200,
">0",t1:t200)
Then create a function in cell t203 = countif(t1:t200, ">0")
The average of all numbers above 0 will be (t202/t203)
If your intention is to include negative values as well as replace ">0" by
"<>0"
Click to expand...


Did you try those formulas with the non-contiguous cells that the OP specified?
I was not able to get COUNTIF to work with a non-contiguous range.


--ron
 
Here's another way

=SUMPRODUCT((MOD(ROW(T22:T176),14)=8)*T22:T176)/SUMPRODUCT(--(LARGE((T22,T36
,T50,T64,T78,T92,T106,T120,T134,T148,T162,T176),ROW(INDIRECT("1:"&COUNT(T22,
T36,T50,T64,T78,T92,T106,T120,T134,T148,T162,T176))))>0))

It obviously would be nicer to use named the ranges

=SUMPRODUCT((MOD(ROW(T22:T176),14)=8)*T22:T176)/SUMPRODUCT(--(LARGE(MyRange,
ROW(INDIRECT("1:"&COUNT(MyRange))))>0))
 
Ron Rosenfeld said:
Did you try those formulas with the non-contiguous cells that the OP specified?
I was not able to get COUNTIF to work with a non-contiguous range.


--ron
Click to expand...

I agree COUNTIF does not work with non-contiguous range. you may try this:

To find the sum of all the numbers, you may use
=SUM(T22,T50,T64,T78,T92,T106,T120,T134,T148,T162,T176,T36)
To find the count of items that satisfy the condition <>0, you may use
=SUM(T22<>0,T50<>0,T64<>0,T78<>0,T92<>0,T106<>0,T120<>0,T134<>0,T148<>0,T162
<>0,T176<>0,T36<>0)
From this, you can calculate the average in another field.
Alternatively, you may try the function
=SUM(T22,T50,T64,T78,T92,T106,T120,T134,T148,T162,T176,T36)/SUM(T22<>0,T50<>
0,T64<>0,T78<>0,T92<>0,T106<>0,T120<>0,T134<>0,T148<>0,T162<>0,T176<>0,T36<>
0)

The term "SUM" to denote the count of numbers seems to be misleading, but it
works.

Eechhutti R.Rajasekaran
 
Peo Sjoblom said:
Here's another way

=SUMPRODUCT((MOD(ROW(T22:T176),14)=8)*T22:T176)/
SUMPRODUCT(--(LARGE((T22,T36,T50,T64,T78,T92,T106,
T120,T134,T148,T162,T176),ROW(INDIRECT("1:"&COUNT(T22,
T36,T50,T64,T78,T92,T106,T120,T134,T148,T162,T176))))>0))

It obviously would be nicer to use named the ranges

=SUMPRODUCT((MOD(ROW(T22:T176),14)=8)*T22:T176)/
SUMPRODUCT(--(LARGE(MyRange,ROW(INDIRECT("1:"&COUNT(MyRange))))>0))
Click to expand...
....

Begging the question that if MyRange fit a specific pattern, whether MyRange
shouldn't be defined by that pattern, so defining MyRange referring to

=N(OFFSET($T$1,8+14*ROW(INDIRECT("1:INT(1+(176-22)/14))),0,1,1))

then just using the array formula

=AVERAGE(IF(MyRange>0,MyRange))

However, arbitrary multiple area ranges require trickery. If there were no
negative values in such a range, then you could just use

=SUM(MyRange)/INDEX(FREQUENCY(MyRange,0),2)

If there were negatives as well as zeros in MyRange to be excluded, then

SUMPRODUCT(LARGE(MyRange,ROW(INDIRECT("1:"&INDEX(FREQUENCY(MyRange,0),2)))))
/INDEX(FREQUENCY(MyRange,0),2)
 
Back
Top