Google Sheets Average Including Zero Values

Question

I am using the below formula to pull through 2 averages into a single cell. It nearly works as expected although in the one labelled 'C' the cells initially have a '0' in which until they are all completed doesn't show an accurate average as it is including the number of '0's in the division. I've read there is an AVERAGEIF function to exclude them but I can't seem to get this to work, any idea please?

"="W: " & round(average(C34:C40), 2) & " C: " & round(average(G34:G40), 2)"

Answer 1

I believe this should get the job done:

=average(averageif(C34:c40,"<>0")+averageif(g34:g40,"<>0"))

Basically, taking the average of your two ranges excluding the zeros, then finding the average of those two averages.