-1

I have an excel file where I am looking to get the difference between 2 columns only if they are not blank and there is not an 'x' in the second column - I have this formula:

=IF(OR(Q17="",R17="",R17="x",),"",Q17-R17)

In Q17 I have 290 and in R17 I have 290 (Both formatted as numbers)

for some reason the value I am getting in the cell (when formatted as a number is 0.00) but when changed to general or scientific is 5.6843418860808E-14. it is driving me crazy because I have looked at everything and nothing is working. I cannot find what the issue is. Has anyone had any similar issues?

If i change the formula to Q17-R17 it produces the correct value of 0 though..

Hopefully this is okay but here is sample data where I am seeign the issue - its 3 rows all with the same scenario (i have tried reformatting the cells multiple times but nothing seems to work)

http://s000.tinyupload.com/?file_id=02537626623715446740

Sam
  • 27
  • 7
  • Do you have the *literals* 290 in those two cells, or do you have expressions that *evaluate to* 290, basically, did you type in 290? or did you add formulas? (in one or both) – Lasse V. Karlsen Dec 08 '18 at 18:40
  • In your sample file, the numbers are stored as text. – BigBen Dec 08 '18 at 18:45
  • @bigben even if I change them to numbers tho, the same thing still happens – Sam Dec 08 '18 at 18:52
  • Re-enter 406, 1160 and 261 manually and the problem disappears tho... – BigBen Dec 08 '18 at 18:53
  • @bigben, When I manually re-entered I was still getting the error unless I put some other number in other than 290. I am getting these all from a file also so I can’t mAnually change them :( – Sam Dec 08 '18 at 18:56
  • Maybe try something like `=IF(OR(Q17="",R17="",R17="x",),"",CLEAN(Q17)-CLEAN(R17))` – BigBen Dec 08 '18 at 19:04
  • also see: https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/ – Scott Craner Dec 08 '18 at 19:10

1 Answers1

0

Perhaps use the CLEAN function to remove non-printable characters, which seems to be your problem.

 =IF(OR(Q17="",R17="",R17="x",),"",CLEAN(Q17)-CLEAN(R17))
BigBen
  • 46,229
  • 7
  • 24
  • 40