Posted to microsoft.public.excel.misc
|
|
averages
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.
.
|