You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Getting answer FALSE, but cells are equal!

Advertisement

Hi Tom

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

Gail,

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

=Round(P1,2)=Round(L1,2)+Round(M1,2)

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.

--

Regards,

Tom Ogilvy

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

Excel

Answers by Expert:

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.