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 A2 is populated, the formula's output is not blank, which is expected.

A2, B2, & C2 are all populated

Only B2 is blank

Only C2 is blank

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

A2:C2 all blank

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

Only A2 is blank

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


2 Answers 2


Use the following


enter image description here

Functions used:

  • 1
    Is this the only way? I have some ranges that are really long - do I really have to explicitly evaluate each cell?
    – David
    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. Dec 20, 2020 at 0:24
  • Why provide an answer to a duplicate question?
    – Tedinoz
    Dec 20, 2020 at 0:33
  • fair enough, it answered the question as I wrote it. I'll accept this answer
    – David
    Dec 20, 2020 at 4:57
  • You can use IF(CONCATENATE(A2,C2) = "", "", AVERAGE(A2:C2)).
    – Nyerguds
    Apr 30, 2021 at 10:09

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


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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.