Excel IF statement is only returning the false statement

user_17865862 0
Viewed 69

The following Excel IF statement does not return the TRUE value:

=IF(SUM(A1:B1)-TRUNC(SUM(A1:B1))=0,6;ROUNDUP(SUM(A1:B1);0);SUM(A1:B1))

Lets say that A1 and B1 both equials 8,3 this means that SUM(A1:B1) = 16,6

The TRUNC function removes the 8, which leaves the 0,6 0,6 = 0,6 --> should return the TRUE value (17) but it returns 16,6

Actions i have taken so far:

I have removed the ROUNDUP function and tested with a static value and it does not return this statement either--> confirming it only returns the FALSE statement

I have checked the TRUNC function, it returns 0,6

I have made sure my formatting is numeric

I am out of ideas, please help me out.

excel-formula
1 Answers

CLASSIC example of Excel's innate 'floating pt. / precision' error: there's an additional 0.0000000000001 being added to the end of the initial sum - simple round() function will solve the issue:

Screenshot(s) refer:

=IF(ROUND(SUM(A1:B1)-TRUNC(SUM(A1:B1)),9)=0.6,ROUNDUP(SUM(A1:B1),0),SUM(A1:B1))

Eliminating floating pt. error

You can see this by selecting the relevant part of the overall function (in the formula bar) and then pressing F9 (don't forget to press escape after this or Excel will overwrite your function with the evaluated value -- in which case you can also undo with Ctrl + Z):

Evaluating equations in formula bar with F9

I've seen this issue crop up in several (unrelated) applications - here are a couple of examples/instances if you're interested:

  • conditional formatting here
  • Lookups here
user_136076720
Related