=ROUND question

[mrguinness]

I found this response in another forum and it worked for me, but I don't completely understand why...


You can't perform this kind of comparison using floating point numbers without rounding the values first due to the limited precision within Excel and the way computers store values in Binary. Hence you should round both numbers first:
=ROUND(A1,4)=ROUND(B1,4)
Using EXACT is unnecessary - you should only use EXACT for string comparisons where case sensitivity is important.


My question is - the 2 cells I was comparing did have the exact same € amt in them via formulas, but the comparison field kept says FALSE till I plugged in the above formula instead. Can someone explain a bit more in depth why and also, what are the 4's doing in the formula?

[TMS]

the 2 cells I was comparing did have the exact same € amt in them via formulas

Almost certainly, if you increase the number of decimal places displayed, you will find the numbers are not the exact same amount.

The 4 is rounding the numbers to 4 decimal places.

[mrguinness]

Both cells are simple currency fields with 2 decimals.

That's interesting though

[daddylonglegs]

My question is - the 2 cells I was comparing did have the exact same € amt in them via formulas, but the comparison field kept says FALSE till I plugged in the above formula instead. Can someone explain a bit more in depth why and also, what are the 4's doing in the formula?

This might be more "in depth" than you need but this article explains why Excel's calculations can sometimes produce incorrect results

https://learn.microsoft.com/en-us/of...ccurate-result

[TMS]

You are displaying two decimal places. That doesn't mean there are only two decimal places.

As I said, increase the number of decimal places. Eventually, you will (almost certainly) see a difference in the calculated amounts.

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[6StringJazzer]

I'll bet the cells you were comparing did not have the exact same amounts. Format the cells to show 15 decimal places and let us know what you see.

Without knowing the context of what you quoted, I have no idea why they rounded to 4 decimal places. If you are dealing with currency, you are almost always going to round to 2 places, unless you are talking about unit pricing that has greater precision than that, like gasoline.

The reason you have to do the rounding has to do with trying to calculate using decimal numbers in a computer that does binary arithmetic. This is true of all binary computers, not specific to Excel. It gets rather technical and I'm assuming you don't want to go that deep. But here is an analogy. Suppose I want to do the division 1/3. In base 3, it's 0.1, very simple. But in decimal notation, you get a repeating decimal. You cannot represent 1/3 exactly in base 10. Similarly, you cannot represent 1/3 exactly as a binary number. When you do two different calculations and it looks like you are getting the same number, it might not really be the same number because Excel will maintain only 15 significant digits, and that very last digit will have to be rounded. Depending on the value and how the calculation is done, it might round in either direction.

If you really want more detail I'll refer you to an IEEE standard on real number representation.

[TMS]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon (Next to Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.