IF Numbers function not as expected [solved]

TidBITS Talk
1

Numbers IF Function has me a bit lost (I don’t deal in Functions often). :face_with_peeking_eye:

It’s only partly successful so far.

I need a cell to display 0 if a calculation result is 0 or negative. Otherwise it should display the result. Should be simple, but… I’m trying to use IF, in Functions pane:

IF(if-expression, if-true, if-false)

if-expression: A logical expression. if-expression can contain anything as long as the expression can be evaluated as a boolean value. If the expression evaluates to a number, 0 is considered to be FALSE, and any other number is considered to be TRUE.

if-true: The value returned if if-expression is TRUE. if-true can contain [any value](file:///Applications/Numbers.app/Contents/Resources/FunctionHelp.bundle/Contents/Resources/en.lproj/ffa361bc137.html#ffa361bc137). If if-true is omitted (there’s a comma, but no value) and if-expressionevaluates to TRUE, IF returns 0.

if-false: An optional argument specifying the value returned if if-expression is FALSE. if-false can contain any value. If if-false is omitted (there’s a comma, but no value) and if-expression evaluates to FALSE, IF returns 0. If if-false is entirely omitted (there’s no comma after if-true) and if-expression evaluates to FALSE, IF returns FALSE.

I’ve now re-read the instructions about the sixth time and finally it looks like what I’ve entered is acting correctly:

image
image450×340 17.1 KB

but is not what I’m seeking. I need, in the example above, C6 to display “2”. I’ve tried adding a comma after the if-true quotes, and a comma and empty quotation marks but those don’t result in displaying the number in a6.

Is IF the wrong function, or should the syntax be different or what?

(just a nudge in the right direction, or keyword would be sufficient, not asking for paragraphs of explanation, I should be able to figure this out on my own but today, ah…)

2

Try IF(A3<=0, 0, A3) (meaning… if A3 is less than or equal to zero, the formula result is 0; otherwise the formula result takes the value of the cell A3)

Also - I think your formula references are one row out. On row 4, your formula references A3, but I suspect that should be A4…?

2 Likes
3

Thank you @ashley !!! looks like adding the cell reference did the trick! I had tried that earlier but now I think of it put the cell row and column inside quote marks so of course that was displayed, not the result of the formula! I knew it would be something simple like that!

well observed, I made several tests to screenshot for the Topic and in my haste did not update the text in d4 & d6, sorry. The functions in c4 & c6 correctly evaluated a4 & a6 though.

1 Like
4

Just to clarify, the IF function has three parts separated by commas - the test, the result if the test is true, and the result if the test is false. Because your function left off the last part, the result of the function was the binary FALSE when the test was not true.

2 Likes
5

OK and if it helps anyone, just updated the example, hopefully clearer and more fully and correctly, with full evaluation of positve, 0 and neg values:

image
image890×792 34 KB

The displayed values in column d were the same even without the A4 and A6 references in C4 & C6, which upon 7th (or more) reading of instructions, makes sense, as the if-expression was true and no further evaluation was required.

Phew!

2 Likes
6

Thanks @ddmiller , more clearly stated than the instructions! Maybe you could offer the Numbers division to rewrite them! For dabblers in Functions like me, the instructions where it says the if-false part is optional, makes it even more confusing!

1 Like
7

Yes, as you discovered, it is optional, but sometimes you want that if the result you want is simply the binary FALSE. I usually want a result, but I have wanted a simple FALSE before, but even then I’m usually specific and put that as the “else” final portion of the function.

8

Super, thanks @ddmiller ! I use workbooks daily for home accounting, which is nothing fancy and pretty much set for a couple of years.

Currently I’m updating my income tax workbook. I use TurboTax for some years now and following its calculations is a draining mental workout for me. So I’ve developed a bunch of tables with the key numbers, forms and schedules, gave transfer and calculation cells specific borders, and drew lines to show the transfers, to kind of get an idea how one form or schedule ultimately affects income/deductions/tax.

In an ordinary year in our self-employed international situation that’s challenging enough but have done it some years so it’s familiar. This year we added some capital gains and it’s added some complexity and confusion why certain numbers are as they are, so added some form tables which include ‘add line x and y, subtract from c and compare to .058 of form xyz line 4d, enter 0 if 0 or less’ and the like.

When starting the topic I also needed to ‘compare line f to line x and enter the smaller’, and wanted to ask what function there is for comparing two cells but came across one I’d already done using the MIN function.

Now I just need to figure out why TTax didn’t enter a number on a line despite the form instructions saying it should do so. But that’s a Topic for another forum. Thanks for caring enough to write!