Results 1 to 11 of 11
Thread: Excel Formula (Excel 97 SR2)

20021105, 16:46 #1
 Join Date
 Sep 2001
 Location
 USA
 Posts
 16
 Thanks
 0
 Thanked 0 Times in 0 Posts
Excel Formula (Excel 97 SR2)
Hello! I am looking for a formula to average a column of numbers but not count the 0's in the average. I am currently using AVERAGE but it takes the 0's into account. I am sure there is a way to do this but I am having a brain freeze. Thanks!!
Example: 23, 100, 50, 0, 44, 60, 30 = 307 / 6 = 51.17 I want to divide by 6 rather than 7.

20021105, 17:06 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Excel Formula (Excel 97 SR2)
use for example: (assuming A1:z1 is the range of interest)
sumif(a1:z1,"<>0")/countif(a1:z1,"<>0")
Steve

20021105, 17:15 #3
 Join Date
 Jul 2001
 Location
 Long Beach, California, USA
 Posts
 233
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Formula (Excel 97 SR2)
Hello Marie,
In the book Excel 2000 Formulas by John Walkenbach, he gives examples of how to return the average of a range without including zeros:
=SUM(A1:A10)/COUNTIF(A1:A10, "<>0")
This array formula also works:
=AVERAGE(IF(A1:A5<>0,A1:A5))
when you are done entering this formula press the following keys CTL+SHIFT+ENTER

20021105, 17:41 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Excel Formula (Excel 97 SR2)
sumif / countif can have advantages over sum/countif if there are any cells with errors as it will ignore them also.
Steve

20021105, 18:38 #5
 Join Date
 Sep 2001
 Location
 USA
 Posts
 16
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Formula (Excel 97 SR2)
I think I have to give a better example. I don't have a range of cells but rather set cells in a form.
Example: (F231+F243+f254+f265+f276) I don't think the Countif will work.
This is what I have so far....it returns a value but it isn't correct as it is only adding.
=AVERAGE(IF(F231+F243+f254+f265+f276,>0,F231+F243+ f254+f265+f276))
THANKS!!!!

20021105, 19:21 #6
 Join Date
 Feb 2001
 Location
 Dublin, Ireland, Republic of
 Posts
 2,697
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Excel Formula (Excel 97 SR2)
If the number of cells involved is not much different from your example someting like
SUM(F231,F243,F254,F265,F276)/COUNT(F321,F243,F254,F265,F276,">0")
should work
Andrew C

20021105, 19:40 #7
 Join Date
 Sep 2001
 Location
 USA
 Posts
 16
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Formula (Excel 97 SR2)
Nope still doesn't work. Do I have something incorrect?
SUM(F231,F243,F254,F265,F276)/COUNT(F231,F243,F254,F265,F276,">0")
Basically it is saying 3+3+3+3+3+0 =12 / 5 = 2.4 It should be 3

20021105, 19:53 #8
 Join Date
 Sep 2001
 Location
 USA
 Posts
 16
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Formula (Excel 97 SR2)
I can't even space the rows by 11 as this is a form.

20021105, 20:16 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Excel Formula (Excel 97 SR2)
Then you will have to bruteforce and hardcode the cells:
=SUM(F231+F243+F254+F265+F276)/SUM(F231>0,F243>0,F254>0,F265>0,F276>0)
Steve

20021105, 20:16 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Excel Formula (Excel 97 SR2)
If you evenly space the rows by 11
F231+F243+f254+f265+f276 to
F232+F243+f254+f265+f276
you could use the array formula (ctrlshiftenter to confirm)
=AVERAGE(IF((MOD((ROW($F$232:$F$277)232),11)=0)*($F$232:$F$277>0),$F$232:$F$279))
Steve

20021106, 00:17 #11
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Formula (Excel 97 SR2)
Two things:
1 COUNT(F231,F243,F254,F265,F276,">0") does not make sense. The first five arguments will be counted if the corresponding cells contain a number, even if that number is zero. The last argument will never be counted since it is a string (">0") and not a number.
2 The SUM function has five arguments, but your 3+3+3+3+3+0 example is six numbers.
I think that you will need a formula something like this:
<pre>=SUM(F231,F243,F254,F265,F276)/SUM(F231>0,F243>0,F254>0,F265>0,F276>0)
</pre>
Legare Coleman