Combining Averageif and Averagea: take a conditional average treating blanks as 0

Question

I need to calculate an average conditioned over a range that can contain empty cells or the empty strings ("").

The formula AVERAGEIF like AVERAGE ignore the empty cells. The formula AVERAGE, has an 'alter ego' that converts text cells to 0 value: AVERAGEA

I haven't found a formula that joins AVERAGEIF with AVERAGEA. Anyone have an idea to resolve the problem and treat empty cells as 0?

Answer 1

You can add 0 to convert empty strings and blank spaces to 0. Then use AVERAGE. Example:

=average(arrayformula(A1:A15 + 0))

calculates the average of all numbers, treating "" and blanks as 0.

One needs arrayformula here to handle adding 0 to each element of the range.