Excel/Getting answer FALSE, but cells are equal!
I am using the formula =P1=L1+M1 to try show if the difference between L1 anD M1 is equal to P1, and am getting FALSE as the answer (see below). However, L1+M1 is equal to P1, at least to the two decimal places I can see. Do you think the FALSE result is because of further decimal places I cant see, and if so, is there a way of making the formula look only at 2 dp?
L1 M1 N1 P1
75,799,878.18 -73,974,228.04 FALSE 1,825,650.14
In excel, all numbers are stored with 15 digits of precision and when you are trying to do equality that often causes a problem. (Of course 1/2 is .5 so no problem there) Let's take our decimal system. You know that 1/6 + 1/6 = 1/3
but if we tried to write it as a decimal value, it would never be exactly 1/6. We could write 0.166666666666666666667 but never exactly 1/6. And adding the representations together, the sum will not exactly equal .3333333333. This is a two fold problem because both sides of the equation are imprecise.
same when we store a number as binary. Some fractions can not be represented exactly. Excel follows the IEEE standard for representing floating point numbers.
So you are exactly correct in that your problem is based on decimal digits you do not see unless you format the cell to show more decimal places. You can look in the formula bar with the cell select and should be able to see the additional decimals.
to solve you would do
Note that this is a general computer storage problem (just like in the decimal system) and not just an excel problem. There is an option in excel to do Precision as displayed but when you set it for that workbook, the values are inherently rounded to the format precision and stored that way. The setting can not be reversed once applied.
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here