Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA isn't subtracting properly
Hi All,
I have this small function in a worksheet (as this is where the rest of my code is written) that's just not wanting to work. It is as follows: Private Function fncIsCountingNumber(dSomeNumber As Double, dTotal _ As Double) As Boolean Dim dRoundQ As Double, dQ As Double, dDiff As Double If dSomeNumber = 0 Then fncIsCountingNumber = False Else dRoundQ = Round(dTotal / dSomeNumber, 0) dQ = dTotal / dSomeNumber dDiff = dQ - dRoundQ If dDiff = 0 Then fncIsCountingNumber = True Else fncIsCountingNumber = False End If End If End Function I am plugging in .01 for dSomeNumber, and 4.98 for dTotal. This results in a value of 498 for dQ, and 498 for dRoundQ (which is to be expected). However, for some reason, my value for dDiff is 5.6843418860808E-14. Any thought on this. Perhaps there's another method I could try? It would be kind of lame to have to do a round(dDiff,12) to get 498-498=0, but right now, that looks like the most promising solution. I'm using Excel 2003 and VBA 6.3. Thanks. Regards, That One Guy |
#2
Posted to microsoft.public.excel,microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA isn't subtracting properly
Hi All,
Nevermind. I found my answer. My workaround: Since there really are only 3 significant figures anyway, I'm using currency. I found the reason double won't work for equitable comparisons at http://stackoverflow.com/questions/2...cision-problem. Regards, That One Guy. That One Guy wrote: Hi All, I have this small function in a worksheet (as this is where the rest of my code is written) that's just not wanting to work. It is as follows: Private Function fncIsCountingNumber(dSomeNumber As Double, dTotal _ As Double) As Boolean Dim dRoundQ As Double, dQ As Double, dDiff As Double If dSomeNumber = 0 Then fncIsCountingNumber = False Else dRoundQ = Round(dTotal / dSomeNumber, 0) dQ = dTotal / dSomeNumber dDiff = dQ - dRoundQ If dDiff = 0 Then fncIsCountingNumber = True Else fncIsCountingNumber = False End If End If End Function I am plugging in .01 for dSomeNumber, and 4.98 for dTotal. This results in a value of 498 for dQ, and 498 for dRoundQ (which is to be expected). However, for some reason, my value for dDiff is 5.6843418860808E-14. Any thought on this. Perhaps there's another method I could try? It would be kind of lame to have to do a round(dDiff,12) to get 498-498=0, but right now, that looks like the most promising solution. I'm using Excel 2003 and VBA 6.3. Thanks. Regards, That One Guy |
#3
Posted to microsoft.public.excel,microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA isn't subtracting properly
On Wed, 11 Feb 2009 17:38:49 -0800, That One Guy wrote:
Hi All, I have this small function in a worksheet (as this is where the rest of my code is written) that's just not wanting to work. It is as follows: Private Function fncIsCountingNumber(dSomeNumber As Double, dTotal _ As Double) As Boolean Dim dRoundQ As Double, dQ As Double, dDiff As Double If dSomeNumber = 0 Then fncIsCountingNumber = False Else dRoundQ = Round(dTotal / dSomeNumber, 0) dQ = dTotal / dSomeNumber dDiff = dQ - dRoundQ If dDiff = 0 Then fncIsCountingNumber = True Else fncIsCountingNumber = False End If End If End Function I am plugging in .01 for dSomeNumber, and 4.98 for dTotal. This results in a value of 498 for dQ, and 498 for dRoundQ (which is to be expected). However, for some reason, my value for dDiff is 5.6843418860808E-14. Any thought on this. Perhaps there's another method I could try? It would be kind of lame to have to do a round(dDiff,12) to get 498-498=0, but right now, that looks like the most promising solution. I'm using Excel 2003 and VBA 6.3. Thanks. Regards, That One Guy Your result is a consequence of the fact that computers think in binary, and decimal often cannot be exactly expressed in binary. Some of your options: Round Test for no greater than a small difference Used the Decimal data type --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF being used properly? | Excel Worksheet Functions | |||
F2 key is not functioning properly | Excel Discussion (Misc queries) | |||
But not working properly | Excel Discussion (Misc queries) | |||
MODE isn't working properly. | Excel Worksheet Functions | |||
Does not add up properly | Excel Discussion (Misc queries) |