How to make IF function in Google Spreadsheets output nothing?

Question

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

Answer 1

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

Answer 2

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.