Is there a formula that has the effect of averaging all the non-zero number-valued cells in a range of cells in excel?
Ah–looks like the answer is here.
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.
You could also do sum/counta, which is slightly simpler. Counta ignores empty/zero-value cells.
Here is a better guide to array formulas:
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 