averages

[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.

 

[T. Valko]

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)

 

[ShaneDevenshire]

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)

 

[T. Valko]

If you are going to use the last suggestion it would be safer to use

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

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.

 

[Very Basic User]

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!...

 

[Bernard Liengme]

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

 

[Very Basic User]

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

 

[Very Basic User]

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))

 

[Bernard Liengme]

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