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