Mar 09 2021 11:38 AM - edited Mar 09 2021 11:40 AM
While designing a spreadsheet in Excel 365, I was inputting an IF() function. Support documentation indicates that the [value_if_true] and [value_if_false] responses are optional. There is a problem when one or both are left blank. The logical value for TRUE is zero (0), but then again, so is the logical value for FALSE. I've always understood that TRUE is supposed to have a logical value of one (1), and FALSE will be zero (0). To test this, open the attached spreadsheet or create a new one and put random numbers in cells A1 and A2. In cell A3, put the following formula "=if(A1=A2,,). No matter what the values in A1 and A2, this will always evaluate to a logical zero (0), but there's no way to determine if it's meant to be TRUE or FALSE. I've seen spreadsheets designed with just such a formula in them, as the programmer wasn't sure at the time where he/she was going to get the result for condition TRUE, or the result for condition FALSE. The intent was to change the formula at a later time. While this doesn't happen often, it can be a tricky bug to find, as the formula is considered valid. You can even run data validation on it, and it will always end with a value of zero (0). If any text is used for one of the condition results, that text will be filled in, instead of zero (0). It's possible to really mess up the formula by having something like this: "=IF(A1=A2,FALSE,TRUE)". While the equation is syntactically correct, the result could be disastrous. I've attached a spreadsheet with some samples.
Mar 09 2021 11:56 AM
If you omit value_if_true, the return value will NOT be TRUE if the condition is met. From IF function:
Problem |
What went wrong |
---|---|
0 (zero) in cell |
There was no argument for either value_if_true or value_if_False arguments. To see the right value returned, add argument text to the two arguments, or add TRUE or FALSE to the argument. |
So you should always provide both value_if_true and value_if_false
Mar 09 2021 11:56 AM
=if(condition,,) is equal to =if(condition,<blank cell value>,<blank cell value>), or =if(condition,0,0)
Other words that's =if(condition, FALSE, FALSE)
IMHO, that's not a bug.
As note, that's not blank as a value, it's not supported so far.
Mar 09 2021 11:59 AM
Mar 09 2021 12:06 PM
They are optional, but the result of =IF(condition, , ) will not be TRUE or FALSE, but 0, as you have found.
By the way, if you omit the second comma and if the condition is FALSE, the result will be FALSE:
=IF(4>5, "Yes") returns FALSE.
Mar 09 2021 12:50 PM
Optional parameters quite often (not always) return zero. For example, =MAX(-1,) gives 0.