# =if() function is not working like it should be

I am trying to use an `IF` function in a formula in MS Excel 2013, but every time the `IF` function ignores the condition I wrote and goes to the true value. I checked my `IF` statement over and over, and I don't see anything weird or wrong.

This is the equation I used:

``````=IF(F17>0,(ABS(D17)/100*G16)+G16,(G16-((ABS(D17)/100)*G16)))
``````

First, it checks the value of `F` column to see if it's larger or smaller than `0`, then it does some math based on the `IF` being true or false.

The problem is that it always calculates the true value even if the `F17` contains a negative number.

• ...`F17` references the cell `F17`, not the entire column.
– Bob
May 24, 2013 at 15:42
• I know I just wrote that equation as an example but in the document it goes like F1,F2,F3,F4,.....,etc May 24, 2013 at 15:45
• @faisal60 could you perhaps post a screenshot (of some dummy values if the information is sensitive)? May 24, 2013 at 15:58
• This is working for me. I put in a few dummy values and used the test `=IF(F17>0,"F17 > 0","F17 < 0")` and it worked as intended. The actual formula switched positive/negative when true/false. May 24, 2013 at 17:46
• This isn't exactly programming, but this rule applies anyway: Select isn't broken. May 25, 2013 at 7:37

=IF(F1>0,TRUE,FALSE)

and copy down. If the result shows TRUE for all rows, then your source data is the problem. You may have text that looks like numbers. • It's even easier than this. On the Formulas tab, select Evaluate Formula. It will step you through the formula so you can see where the error is. Jul 26, 2013 at 19:01

Check to see if your data is formatted as a number value or as text. If it is formatted as text, then the comparison `F17>0` will always evaluate to `TRUE`. The workaround is to use the `VALUE()` function in your formula. In your case, you'll want to use the following formula:

``````=IF(VALUE(F17)>0,(ABS(D17)/100*G16)+G16,(G16-((ABS(D17)/100)*G16)))
``````

Of course, beware that some of the other cells you reference may contain text-formatted numbers as well, so adjust accordingly.

Unnecessary complexity tends to make things harder.  For starters, you've got a pair of parentheses that you don't need.  (And, by the way, spaces make things easier to read.)

``````                                     ↓                        ↓
=IF(F17>0,  (ABS(D17)/100*G16)+G16,  (G16-((ABS(D17)/100)*G16)) )
``````

is equivalent to

``````=IF(F17>0,  (ABS(D17)/100*G16)+G16,   G16-((ABS(D17)/100)*G16)  )
``````

A trivial rearrangement yields

``````=IF(F17>0, G16 + (ABS(D17)/100*G16), G16 - ((ABS(D17)/100)*G16) )
``````

and at this point the common terms are jumping off the page.  The above can be simplified to

``````=G16 + IF(F17>0, (ABS(D17)/100*G16), -((ABS(D17)/100)*G16) )
``````

and hence to

``````=G16 + IF(F17>0, 1, -1) * (ABS(D17)/100)*G16
``````

and now another set of parentheses becomes redundant:

``````=G16 + IF(F17>0,1,-1) * ABS(D17)/100 * G16
``````

And guess what:

``````=G16 + SIGN(F17) * ABS(D17)/100 * G16
``````

F17 might not be at the correct number format, I suggest you write `=VALUE(F17)>0` somewhere in your spreadsheet. If the result is false, then it means your number in F17 was stored as a text.

• Welcome to Super User. Here we have a policy of not including signatures to the content you post. Could you edit these out? Apr 25, 2016 at 15:50

I just had the same issue and it turns out that in my version, Office 365 16.0.13628.20128, the separator between condition, true statement and false statement had to be semicolon instead of comma:

e.g. `=IF(SOMETHING; TRUE; FALSE)` instead of `=IF(SOMETHING, TRUE, FALSE)`.

• This is a LOCALE dependency. i.e. Language and region settings. Apr 8, 2021 at 15:22

This is supporting input for adding the VALUE function.

I had nested `IF(RIGHT(INT(X*Y*Z)),2>50),ROUNDUP(INT(X*Y*Z),-2),ROUNDDOWN(INT(X*Y*Z),-2)` and it was not working -- even though each of the formulas worked fine separately and resolved to the correct rounding.

But in the Nested formula, it only worked when I included the VALUE before RIGHT.

``````=if((VALUE(right(INT(G570*120*3*1.0309),2)))>50,ROUNDUP(INT(G570*120*3*1.0309),-2),ROUNDDOWN(INT(G570*120*3*1.0309),-2))
``````

If F17 is 0 but was previously referenced as "0" in a formula, it will be considered a text and not the number 0. Make sure 0 is really the number zero.

You can test this by doing:

`IF(F17>0, "TRUE", "FALSE")`
and compare it to
`IF(*another cell you manually type zero in*, "True", "False")`

If the answers are different from each other, the F17 might be interpreted as text by your formula. To rectify this, check the formula in F17 to see if the answer is in quotation marks, and remove the quotation marks to make the answer a number.