# Thread: Excel Formula (Excel 97 SR2)

1. ## 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.

2. ## 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

3. ## 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

4. ## 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

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

6. ## 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

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

8. ## Re: Excel Formula (Excel 97 SR2)

I can't even space the rows by 11 as this is a form.

9. ## Re: Excel Formula (Excel 97 SR2)

Then you will have to brute-force and hardcode the cells:

=SUM(F231+F243+F254+F265+F276)/SUM(F231>0,F243>0,F254>0,F265>0,F276>0)

Steve

10. ## 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 (ctrl-shift-enter to confirm)
=AVERAGE(IF((MOD((ROW(\$F\$232:\$F\$277)-232),11)=0)*(\$F\$232:\$F\$277>0),\$F\$232:\$F\$279))

Steve

11. ## 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>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•