Full Version: Why Does This If Function Work?
ILGal
Hi, everyone. I just received a spreadsheet with a column of values that begin in D4. In the adjacent E column, an IF function is assigning a \$25 carrying charge for any balance >0 remaining in the D column.

For the function in E4, I would expect to see: =IF(D4>0,25,0).
Instead, the function reads: =IF(D4,25,0) and it is working correctly (displaying either 25 for D column entries with balances or 0 for those without).

Why is this working, if, in the first argument, D4 is not being compared to anything?

Thanks for any insight!
theDBguy
Hi,

It works because the IF function evaluates a condition to be true or false. In most systems, false is represented by 0, and true is represented by -1. However, some systems will return true if the condition does not evaluate to a 0.

Just my 2 cents...
ILGal
Hi, DBGuy. Your assumption seems to bear out. I put some numbers in cells, and then opened the Insert Function dialog. In the first (logical_test) argument box, I just clicked a cell with the value 0, and the prompt showed "false". A blank cell also returned a "false" value. Other nparing the value, if there is nothing for comparison? numbers, whether positive OR negative, returned a TRUE value. It does seem that, in the absence of a true comparison, Excel assumes the logical test to be <>0, as in, =IF(B4<>0,25,0). It would seem more useful to display an error, however, if the cell referenced in the logical_test argument did not contain a true comparison, because the results could lead to misinformed decisions (as in charging a carrying charge on a negative balance if that were not the intended purpose).
theDBguy
That is why I would prefer to be explicit in my expressions instead of assuming how the system might evaluate the result of a condition.

Just my 2 cents...
ipisors
Just to illustrate the point more, I was reflecting on this and thinking: The opposite of this is what I have come across a few times in spreadsheets created by others in my company. I have occasionally seen things like:

=IF(D4>0=TRUE,25,0)

Which can be thought of as taking things to the exact opposite extreme as you saw in your spreadsheet. It is kind of an interesting discussion. Just my opinion, but from what I have seen at least, either extreme is not at all normal practice.