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.
9 Answers
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.
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)*G16and now another set of parentheses becomes redundant:
=G16 + IF(F17>0,1,-1) * ABS(D17)/100 * G16And guess what:
=G16 + SIGN(F17) * ABS(D17)/100 * G16F17 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.
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))I just had the same issue and it turns out 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)
1If 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 toIF(*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.
1I 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.
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
