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:A100,A1:A10))

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

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
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.

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)


--
Thanks,
Shane Devenshire


"tommy" wrote:

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.

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.


--
Biff
Microsoft Excel MVP


"ShaneDevenshire" wrote in
message ...
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)


--
Thanks,
Shane Devenshire


"tommy" wrote:

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.

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!...
--
Thank you for your time!
John


"T. Valko" wrote:

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.


--
Biff
Microsoft Excel MVP


"ShaneDevenshire" wrote in
message ...
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)


--
Thanks,
Shane Devenshire


"tommy" wrote:

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.

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Very Basic User" wrote in message
...
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!...
--
Thank you for your time!
John


"T. Valko" wrote:

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.


--
Biff
Microsoft Excel MVP


"ShaneDevenshire" wrote in
message ...
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)


--
Thanks,
Shane Devenshire


"tommy" wrote:

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.

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
--
Thank you for your time!
John


"Bernard Liengme" wrote:

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Very Basic User" wrote in message
...
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!...
--
Thank you for your time!
John


"T. Valko" wrote:

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.


--
Biff
Microsoft Excel MVP


"ShaneDevenshire" wrote in
message ...
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)


--
Thanks,
Shane Devenshire


"tommy" wrote:

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.



.

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:A102,A1:A10))

--
Thank you for your time!
John


"T. Valko" wrote:

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:A100,A1:A10))

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

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
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.

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Very Basic User" wrote in message
...
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
--
Thank you for your time!
John


"Bernard Liengme" wrote:

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Very Basic User" wrote in
message
...
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!...
--
Thank you for your time!
John


"T. Valko" wrote:

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.


--
Biff
Microsoft Excel MVP


"ShaneDevenshire" wrote in
message ...
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)


--
Thanks,
Shane Devenshire


"tommy" wrote:

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.



.