# In Excel, average all non-zero values

**URL:** <https://boards.straightdope.com/t/in-excel-average-all-non-zero-values/621288>\
**Category:** Factual Questions\
**Created:** [May 8, 2012, 4:06pm UTC](https://boards.straightdope.com/t/in-excel-average-all-non-zero-values/621288 "2012-05-08T16:06:15Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Frylock](https://avatars.discourse-cdn.com/v4/letter/f/ce7236/32.png) [@Frylock](https://boards.straightdope.com/u/Frylock)\
**Post date:** [May 8, 2012, 4:06pm UTC](https://boards.straightdope.com/t/in-excel-average-all-non-zero-values/621288/1 "2012-05-08T16:06:15Z")

</div>

Is there a formula that has the effect of averaging all the non-zero number-valued cells in a range of cells in excel?

---

<div class="post-metadata">

**Author:** ![Frylock](https://avatars.discourse-cdn.com/v4/letter/f/ce7236/32.png) [@Frylock](https://boards.straightdope.com/u/Frylock)\
**Post date:** [May 8, 2012, 4:07pm UTC](https://boards.straightdope.com/t/in-excel-average-all-non-zero-values/621288/2 "2012-05-08T16:07:49Z")

</div>

Ah–looks like the answer is [here](http://www.lytebyte.com/2008/10/16/how-to-calculate-non-zero-average-in-excel/).

---

<div class="post-metadata">

**Author:** ![Darth\_Panda](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@Darth\_Panda](https://boards.straightdope.com/u/Darth_Panda)\
**Post date:** [May 8, 2012, 4:12pm UTC](https://boards.straightdope.com/t/in-excel-average-all-non-zero-values/621288/3 "2012-05-08T16:12:53Z")

</div>

Yes, learning array formulas is very handy.

---

<div class="post-metadata">

**Author:** ![Frylock](https://avatars.discourse-cdn.com/v4/letter/f/ce7236/32.png) [@Frylock](https://boards.straightdope.com/u/Frylock)\
**Post date:** [May 8, 2012, 4:30pm UTC](https://boards.straightdope.com/t/in-excel-average-all-non-zero-values/621288/4 "2012-05-08T16:30:38Z")

</div>

> [@Darth\_Panda](#):
>
> Yes, learning array formulas is very handy.

I couldn’t figure out the array thing on the spot so I just used the sum/countif method.

---

<div class="post-metadata">

**Author:** ![Lance\_Steele](https://avatars.discourse-cdn.com/v4/letter/l/ecae2f/32.png) [@Lance\_Steele](https://boards.straightdope.com/u/Lance_Steele)\
**Post date:** [May 8, 2012, 8:43pm UTC](https://boards.straightdope.com/t/in-excel-average-all-non-zero-values/621288/5 "2012-05-08T20:43:10Z")

</div>

You could also do sum/counta, which is slightly simpler. Counta ignores empty/zero-value cells.

---

<div class="post-metadata">

**Author:** ![Darth\_Panda](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@Darth\_Panda](https://boards.straightdope.com/u/Darth_Panda)\
**Post date:** [May 9, 2012, 12:03am UTC](https://boards.straightdope.com/t/in-excel-average-all-non-zero-values/621288/6 "2012-05-09T00:03:52Z")

</div>

Here is a better guide to array formulas:

[http://www.cpearson.com/excel/ArrayFormulas.aspx](http://www.cpearson.com/excel/ArrayFormulas.aspx)

In general, array formulas are part of going to the next level in Excel. If you can use them well, you can do stuff that other people can barely imagine - like solve for the sum of the squared differences of a series of numbers and other fun stuff 🙂