May 12 2012, 09:13 AM
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!
May 12 2012, 09:46 AM
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...
May 12 2012, 05:34 PM
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).
May 12 2012, 05:53 PM
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...
May 14 2012, 09:26 AM
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:
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.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here