how to paste excel formula correctly

user_8669303 0
Viewed 59
  • I made cell A1 equal 1.39139
  • Made cell B1 equal 1.40596
  • Made cell C1 a formula =(A1-B1)*10000
  • Copied cell C1 and pasted it by value into cell D1
  • In cell E1 I wrote manually the real result of the calculation, which is -145.7 (you can try using a calculator).

enter image description here

  • In cell F1 I made an if statement to compare the results of E1 and F1: =IF(E1=D1,"equal","not equal")
  • The result is "not equal".

enter image description here

**I want to know how to copy and paste a formula and make sure its pasted result will be exactly the same. keep in mind that I don't want to use =round() formula because I need a solution for many numbers, and I can't use round() for each cell with different number of digits.

excel-formula excel
1 Answers

As already pointed out, this is because of Excel's floating point precision.

You are correct that (1.39139-1.40596)*10000 is equal to -145.7. However, I tried typing this into Excel to see what it actually produced and I found the following. If you highlight your cell C1 and press F9 on the keyboard you will see that Excel actually computes this value as -145.700000000002.

I know you said you don't want to use ROUND but, aside from avoiding floating point numbers altogether (i.e. only using integers), I think ROUND is your only option.

I suggest that you determine what is the maximum number of digits after the decimal you will ever need in your sheet, and incorporate ROUND(<number>,<max digits>) into all formulas as necessary.

i.e. instead of using =(A1-B1)*10000, you should type =ROUND((A1-B1)*10000,5) (for example to round to 5 digits) which would return the value of exactly -145.7. I hope this helps, or if nothing else, I hope this at least shed light on what is causing this.

user_83706330
Related