I am using Excel 2010 I am having trouble with math in some VBA code. I am adding and subtracting some figures in a column according to some criteria and when I do this with figures entered containing 3 decimal places the results are not what I expect.
So, I add 4032.258 + 773.994, which yields 4806.252; but when I use an if statement to test whether there is sufficient (without going negative – it should =0) to take 4806.252 from that total, I don’t get Zero, I get something akin to '-9.09494701772928E-13'.
iQty holds the total of the sum of 4032.258 + 773.994 – nQty holds the amount 4806.252
My If statement looks like this; If iQty >= nQty Then ' I expect=TRUE, enough or more than enough
The code above rejects the comparison.
I am using Double's as my variables to hold the numbers as I add and subtract them, I suspect this is the issue but am not sure what to use instead.
Any help much appreciated. Thanks
It is due to how floating-point numbers are stored and calculated in Excel ( IEEE 754 standard). Long story (explained by Microsoft here and here) short use round function to some precision:
If Round(iQty,5) >= Round(nQty,5) Then