ISBLANK function returning unexpected results

Question

I want to show the average of a range if any cell is populated. However, my formula shows the average only if the first cell is populated.

I'm using the below formula:

=IF(ARRAYFORMULA(ISBLANK(A2:C2)),"",AVERAGE(A2:C2))

If A2 is populated, the formula's output is not blank, which is expected.

And if A2:C2 is all blank, the formula's output is blank as expected.

However, if A2 is blank while B2 and C2 are populated, the formula's output is blank, which is not what I want:

How do I get the output of the formula to be blank only if the entire range is blank?

Does this answer your question? How to write AverageIf (Not an Empty String) in Google Sheets? — Tedinoz, Commented Dec 20, 2020 at 0:17
Referencing How to write AverageIf (Not an Empty String) in Google Sheets?, this is the best answer. — Tedinoz, Commented Dec 20, 2020 at 0:32
@tedinoz Thanks for the link! I like the IF(COUNT(),,) solution, it lets me do this without referencing each cell explicitly — David, Commented Dec 20, 2020 at 4:58
It also has the advantage that it doesn't return DIV/0 error when all the cells are blank. — Tedinoz, Commented Dec 21, 2020 at 4:12

marikamitsos · Accepted Answer · 2020-12-20 00:20:15Z

0

Use the following

=IF(AND(A2="",C2="",B2=""),"",AVERAGE(A2:C2))

Functions used:

answered Dec 20, 2020 at 0:20

marikamitsos

13.1k4 gold badges34 silver badges59 bronze badges

1

Is this the only way? I have some ranges that are really long - do I really have to explicitly evaluate each cell?
– David
Commented Dec 20, 2020 at 0:21
No. Certainly not. It is just your formula corrected. See the comment on your question. If still in trouble, please give an example of your ranges.
– marikamitsos
Commented Dec 20, 2020 at 0:24
Why provide an answer to a duplicate question?
– Tedinoz
Commented Dec 20, 2020 at 0:33
fair enough, it answered the question as I wrote it. I'll accept this answer
– David
Commented Dec 20, 2020 at 4:57
You can use IF(CONCATENATE(A2,C2) = "", "", AVERAGE(A2:C2)).
– Nyerguds
Commented Apr 30, 2021 at 10:09

Add a comment |

NDavis · Accepted Answer · 2021-09-09 23:28:44Z

5

To check if ALL cells in range A2:C2 are blank

=AND(ARRAYFORMULA(ISBLANK(A2:C2)))

To check if ANY cell in range A2:C2 is blank

=OR(ARRAYFORMULA(ISBLANK(A2:C2)))

answered Sep 9, 2021 at 23:28

NDavis

1531 silver badge5 bronze badges

Add a comment |

2 Answers 2