correct Average formula

Occasional Contributor

Cells H3 to L3 is referencing data from a different tab. If it's blank, it shows up as "-". What is the correct average formula to ensure it will only calculate if there are information on it (in this case H3 to J3)





2 Replies
best response confirmed by Sergei Baklan (MVP)


Is the "-" a text string, or a 0 value displayed as "-" because of the number format?

If the former, just use =AVERAGE(H3:L3) for AVERAGE ignores text values.

If the latter, and if the value of the source cells will not be 0, you can use =AVERAGEIF(H3:L3,"<>0")

thanks!! It was the latter, and that worked!