3

How do I output nothing with an IF function?

I have created this nested function that takes the average of three cells. However, if one of those cells is empty, it should only take the average of the other two numbers. For this I have established this function:

=AVERAGE((IF(B5="",,SQRT((B3-B5)^2))),(IF(B6="",,SQRT((B3-B6)^2))),(IF(B7="",,SQRT((B3-B7)^2))))

Broken down: if cell1 is empty, output nothing, if it is not empty, output the difference)

To achieve spreadsheets to output nothing I've tried setting the second parameters as "" or . When outputting "", it seems to output nothing, but as a string. so the average function fails on that. when outputting , it seems to actually output 0. The last option obviously messes up the calculation for the average.

I cannot seem to figure out how to output nothing at all.

I've refined my issue to a single spreadsheet that serves as an example for this post. In cell C8 is the correct result, but achieved by splitting the calculation in steps. Cell B8 is the entire formula in one cell, which should achieve the same result. It does this, when all of the values in B5:B7 are filled in. When you leave one cell blank, the results are different. The different results is equivalent to inserting 0 in C5:C7.

Am I missing something obvious here in regards to outputting nothing or am I over engineering this formula and is there an easier, built in function to achieve the same result?

Link to the example: https://docs.google.com/spreadsheets/d/1Pbirr45aqsVxo1CZo2cRsB6-tkEu-7L4KAouxoJBUq0/edit#gid=0

2 Answers 2

2

Try this:

  =AVERAGEIF({(IF(B5="",,SQRT((B3-B5)^2))),(IF(B6="",,SQRT((B3-B6)^2))),(IF(B7="",,SQRT((B3-B7)^2)))},">0")

Please note that the parameter for value to return when true is empty

1
  • This worked. Thanks a lot anonymous bat for the solution. (I still do not understand what the error in my original formula was however, but I can continue for now) Sep 20, 2017 at 16:05
3

AVERAGE will take into account a 0 but ignore a blank, so all you seem to need is:

=average(C5:C7)

Returns 3.33 for [2,8,0] (three entries) and 5 for [2,8,].

Judging by the accepted A, what OP wants is indeed something like:

=AVERAGE(IF(B5<>"",B3-B5),IF(B6<>"",B3-B6),IF(B7<>"",B3-B7))

(without the pointless square rooting of squares). That is, FALSE is ignored in an AVERAGE - unless an average of nothing but FALSEs.

2
  • Thanks for the contribution. I understand this, however there is no difference when setting either =average(F5;F6;F7) or =average(F5:F7). Is this correct? I cannot use the latter because it does not allow me to perform separate calculations on the parameters on which I want to perform AVERAGE. Sep 20, 2017 at 15:21
  • Ok, thanks for pointing this out. This still leaves me with the issue of the IF-formula outputting a 0 instead of nothing. Sep 20, 2017 at 15:28

Your Answer

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

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