1

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.

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?

4
0

Use the following

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

enter image description here

Functions used:

5
  • 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 '20 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 Dec 20 '20 at 0:24
  • Why provide an answer to a duplicate question? – Tedinoz Dec 20 '20 at 0:33
  • fair enough, it answered the question as I wrote it. I'll accept this answer – David Dec 20 '20 at 4:57
  • You can use IF(CONCATENATE(A2,C2) = "", "", AVERAGE(A2:C2)). – Nyerguds Apr 30 at 10:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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