averaging nonzero data

[kevins_3121]

I am averaging water meter readings for a municipality. I need to exclude any zero readings and take the average. What function do I use?

[oldchippy]

Have a look at this link

http://www.cpearson.com/excel/lists.htm

[kevins_3121]

This doesn't seem to be working for me. I keep getting a "#value!" error. Will it work horizontally as well as vertically (as shown in the example)?

[oldchippy]

All of the formulas on this page are array formulas, so you must press Ctrl+Shift+Enter rather than just Enter when you enter the formula, and whenever you edit it later.

Did you do that?

[kevins_3121]

nope. never heard of them. I guess I need to go back to excel school. Thank you again for your help. That worked that time.

[oldchippy]

Don't worry about it, we're always in a hurry to get things done and don't take it all in - glad it worked for you, thanks for the feedback

[starguy]

I am averaging water meter readings for a municipality. I need to exclude any zero readings and take the average. What function do I use?

if your readings are in col A then try this

=SUM(A1:A50)/COUNTIF(A1:A50,"<>0")