Average to not include "0" or blanks cells

[gr8trthanu]

Good Morning,
I have data in cells C4,I4,O4,U4,AA4 that I require to find the average of and display in AL4.

I do not want "0" or blank cells to be included in the average.

I tried using the following formula but getting an error because the cells are not in a range

*****CODE******
=AVERAGEIF(C4:AA413,"<>0")

Is there another method to this?

Thanks in advance.

 

[jkpieterse]

Try this array formula:
=AVERAGE(IF(c4:AA413<>0,c4:AA413,""))
confirm with control+shift+enter.

 

[gr8trthanu]

Thank you for the response - the formula did not work.

Would this formula also include other cells that fall between C4 and AA413.

I only want the averages of the following cells - C4,I4,O4,U4,AA4

 

[Trouttrap2]

Try this: I did not come up with this formula though. It will ignore zeros. Excel will consider a blank as a zero in this case.
=SUM(C4,I4,O4,U4,AA4)/INDEX(FREQUENCY((C4,I4,O4,U4,AA4),0),2)

 

[gr8trthanu]

Hi Trouttap2 - that solution worked perfectly!! thank you so much!!

 

[MARZIOTULLIO]

=averageifs(c4:aa13,c4:aa13,"<>",c4:aa13,">0")