Forum Discussion
Possible error in IF function
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.
5 Replies
=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.
If you omit value_if_true, the return value will NOT be TRUE if the condition is met. From IF function:
Common problems
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
- I agree that value_if_true and value_if_false should always be included, but they are listed by Microsoft Support as optional. Perhaps the IF() function should generate an error if either argument is left out.
Optional parameters quite often (not always) return zero. For example, =MAX(-1,) gives 0.
