averages

  • Thread starter Thread starter tommy
  • Start date Start date
T

tommy

Hi, I have a column with a line of numbers and I have set an average at the
bottom using Autosum but it is dividing the zeros as well is there any way
around this?.

Thanks in advance, Barry.
 
Assuming the numbers are always positive...

Try one of these:

=SUM(A1:A10)/COUNTIF(A1:A10,">0")

This one is an array formula** :

=AVERAGE(IF(A1:A10>0,A1:A10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
Hi,

If you are going to use the last suggestion it would be safer to use
SUM(A1:A10)/COUNTIF(A1:A10,"<>0")

Alternatively in 2007 you should consider this:

=AVERAGEIF(A1:A10,"<>0")

In all versions you could also use

=AVERAGE(IF(A1:A10<>0,A1:A10,""))

This formula is array entered (press Shift+Ctrl+Enter to enter it rather
than Enter)
 
If you are going to use the last suggestion it would be safer to use
SUM(A1:A10)/COUNTIF(A1:A10,"<>0")
Click to expand...

If the numbers are always positive how is that safer? It's actually less
safe than using >0.

The COUNTIF will include text, empty cells, and Booleans.
 
What if I dont' want the error sign if all my #'s are 0. The formula worked
great for me too, but sometimes on a weekend when we don't run a line, there
are 0's for each shift. When this happens, I get the error #DIC/0!...
 
Replace formula with
IF(COUNTIF(A1:A10,"<>0"), SUM(A1:A10)/COUNTIF(A1:A10,"<>0"),"")
or if you have Excel 2007
IFERROR(SUM(A1:A10)/COUNTIF(A1:A10,"<>0"),"")
best wishes
 
Thank you, works great. May I ask two more while your here.

1. Instead of Not 0 or <>0 what if I want it to not count anything greater
unless greater than 2. We have a standar error of + 2, so I don't want to
factor these into the averages either.
2. How can I get this same thing to work with cells that are not together.
My current line that does not work is =AverageIf(A1,A3,A5,">2") I can't seem
to get this to work.

Thanks! John
 
Hello T. Valko,

I also tried the Array formula with good results less all 0's. Actually I
changed it slightly to the following due to a consistent +2 error in our
input. Meaning that if we actually have a value of 0 it can sometimes read up
to 2. So below works great until I have all cells between 0 and 2 then I get
the error. Is there a way to say (If not then "") so if all cells are between
0 and 2 leave the formula cell blank.

=AVERAGE(IF(A1:A10>2,A1:A10))
 
This =AverageIf(A1,A3,A5,">2") will not work as the syntax requires a range
not a list of cells

This =AVERAGEIF(A1:A5,">2") will work if cells A2 and A4 hold non-numeric
data (or are empty)
best wishes
 
Back
Top