1

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.

7
  • ...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
    – faisal60
    May 24, 2013 at 15:45
  • @faisal60 could you perhaps post a screenshot (of some dummy values if the information is sensitive)?
    – Jerry
    May 24, 2013 at 15:58
  • 1
    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.
    – Nathan C
    May 24, 2013 at 17:46
  • 2
    This isn't exactly programming, but this rule applies anyway: Select isn't broken.
    – gronostaj
    May 25, 2013 at 7:37

9 Answers 9

5

Troubleshoot the formula step by step. Start with a simple

=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.

enter image description here

1
  • 1
    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.
    – wbeard52
    Jul 26, 2013 at 19:01
2

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.

enter image description here

The workaround is to use the VALUE() function in your formula.

enter image description here

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.

1

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
1

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.

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

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).

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

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))
0

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.

1
  • Please provide additional details in your answer. As it's currently written, it's hard to understand your solution.
    – Community Bot
    Sep 3, 2021 at 9:36
0

I ended up on this page because an IF formula that I know should be working, wasn't.

Excel 2019

In the end, I deleted the offending column, saved it, re-opened, and started again using a different column.

-1

Make sure the cell your calling doesn't have something like "0" in the formula, the quotes turn the number 0 to a string (text, even is you format cells) therefore using >0 will return a positive outcome

1
  • "it always calculates the true value even if the F17 contains a negative number."
    – fixer1234
    Apr 25, 2019 at 3:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .