averaging separate cells in excel excluding 0 values

[Hopless & Challenged]

Is it possible to average separate cells (i.e. H18,H22,H28,H30 and only
those cells, not columns) And have it display the average of those in B30?
mean while excluding all 0 values?

 

[Mike H]

Hi,

Try this

=AVERAGE(IF(ISNUMBER(MATCH(ROW(H1:H30),{18,22,28,30},0))*(H1:H30>0),H1:H30))

This an array which must be commited using Ctrl+Shift+Enter NOT by simply
pressing Enter. If you do it correctly Excel will put curly brackets {}
around the formula. you cannot type these yourself.

Mike

 

[Mike H]

Ah,

You said exceluding 0 and not greater than zero so you need this slight
modification

=AVERAGE(IF(ISNUMBER(MATCH(ROW(H1:H30),{18,22,28,30},0))*(H1:H30<>0),H1:H30))

Mike