How to avoid rounding when comparing numbers?
Try to simply divide the numbers. If the result is not exactly 1, then
there is a difference. Udo Maury Markowitz schrieb: We're moving data from an Excel sheet into a table in our DB. We copied the data over in the past, but recently found that it was all cut off at the 4th decimal place. This did not occur in the DB itself as far as I can tell, the fields are set to FLOAT, not MONEY. Anyway I've written a macro to roll over the original data again, find if there's a single matching row in the DB, and if so, update it. As a sanity check I look at the values in the DB just to be sure we have to change something. For instance... if rstTrans!Value = theTotal then... this test always passes, even when the numbers are actually different! For instance, in the first case the number on the left is 1,933.46385, and the one on the right is 1,933.4639 So how do I get VB to properly compare these things? Maury |
How to avoid rounding when comparing numbers?
"Udo" wrote:
Try to simply divide the numbers. If the result is not exactly 1, then there is a difference. That doesn't really help. When I read the cell value, even though it clearly has a five-decimal-place number in it, the value returned in VB is four decimals long. In other words if I try to fix the database, it gets "fixed" with the wrong number. Why is it that when I refer to the cell I get a rounded number? Maury |
How to avoid rounding when comparing numbers?
Most probably the problem is the initialization of the variables. The
following piece of code gives the correct result and shows in the local-window all the decimals which are appropriate. The numbers I used we First = 3,4587921, Second = 3,4587919. Dim First As Double Dim Second As Double Dim Result As Double First = Cells(8, 3) Second = Cells(8, 4) Result = First / Second If Result < 1 Then MsgBox ("Not identical") End If Good luck Udo Maury Markowitz schrieb: "Udo" wrote: Try to simply divide the numbers. If the result is not exactly 1, then there is a difference. That doesn't really help. When I read the cell value, even though it clearly has a five-decimal-place number in it, the value returned in VB is four decimals long. In other words if I try to fix the database, it gets "fixed" with the wrong number. Why is it that when I refer to the cell I get a rounded number? Maury |
All times are GMT +1. The time now is 11:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com