Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for fractions of a penny
I'm attempting to write code to determine if the user entered a value
that has a fraction of a cent. Here is my logic dim i as long dim d as double If IsNumeric(.Value) = True And .Value < 0 Then .Value = Abs(.Value) i = CLng(CDbl(.Value) * 100) d = CDbl(.Value) * 100 If Abs(d - i) 0 Then MsgBox "Rounding error detected! Make sure you don't have umbers with values less than 1/100th", vbExclamation End If Else .Value = "" End If It seems to work for most cases however I'd found some specific values that always report "Round error detected!...." Values such as 282.47, 32.41 and 132.70 If I MsgBox i & " " & d the numbers are the same so Abs(d - i) 0 should always be false I don't quite understand what's doing on. Is there a better way to test for fractions of a cent? Eric |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for fractions of a penny
Eric,
How about multiplying the test value by 100, subtract the integer portion and test whats left against zero. eg: 100.47 x 100 = 10047 10047 -int(10047) = 0 ' the value is ok 100.472 x 100 = 10047.2 10047.2 - int(10047.2) = .2 ' user enter a fraction of a cent Roy "Wescotte" wrote: I'm attempting to write code to determine if the user entered a value that has a fraction of a cent. Here is my logic dim i as long dim d as double If IsNumeric(.Value) = True And .Value < 0 Then .Value = Abs(.Value) i = CLng(CDbl(.Value) * 100) d = CDbl(.Value) * 100 If Abs(d - i) 0 Then MsgBox "Rounding error detected! Make sure you don't have umbers with values less than 1/100th", vbExclamation End If Else .Value = "" End If It seems to work for most cases however I'd found some specific values that always report "Round error detected!...." Values such as 282.47, 32.41 and 132.70 If I MsgBox i & " " & d the numbers are the same so Abs(d - i) 0 should always be false I don't quite understand what's doing on. Is there a better way to test for fractions of a cent? Eric |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for fractions of a penny
Eric,
How about just forcing the numbers to two decimal places? '------------------------- Sub xxx() Dim dblNum As Double dblNum = 282.47123 If IsNumeric(dblNum) = True And dblNum < 0 Then dblNum = Abs(WorksheetFunction.Round(dblNum, 2)) MsgBox dblNum Else MsgBox "Your entry should be a number greater than 0. " End If End Sub '----------------------- Regards, Jim Cone San Francisco, USA "Wescotte" wrote in message oups.com... I'm attempting to write code to determine if the user entered a value that has a fraction of a cent. Here is my logic dim i as long dim d as double If IsNumeric(.Value) = True And .Value < 0 Then .Value = Abs(.Value) i = CLng(CDbl(.Value) * 100) d = CDbl(.Value) * 100 If Abs(d - i) 0 Then MsgBox "Rounding error detected! Make sure you don't have umbers with values less than 1/100th", vbExclamation End If Else .Value = "" End If It seems to work for most cases however I'd found some specific values that always report "Round error detected!...." Values such as 282.47, 32.41 and 132.70 If I MsgBox i & " " & d the numbers are the same so Abs(d - i) 0 should always be false I don't quite understand what's doing on. Is there a better way to test for fractions of a cent? Eric |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for fractions of a penny
That's what this code does..
i = CLng(CDbl(.Value) * 100) d = CDbl(.Value) * 100 If Abs(d - i) 0 Then ' Entered value has a fraction of a cent Try 282.47 if I add MsgBox i & " " & d & " " & .Value & " " & Abs(d - i) just after the d = line above the results are 28247 28247 282.47 3.6379788709171E-12 so basically Double(282.47 * 100) - Long(282.47 * 100) != 0 Not sure how to get around thing correctly.. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for fractions of a penny
I thought of that but I need to keep the correct value in the cell and
just not let the user advance until they correct it manually because if I just round everything it will cause a headache trying to figure out where your extra penny went/came from. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for fractions of a penny
Eric,
Why not enter the rounded number into the cell? Jim Cone "Wescotte" wrote in message ups.com... I thought of that but I need to keep the correct value in the cell and just not let the user advance until they correct it manually because if I just round everything it will cause a headache trying to figure out where your extra penny went/came from. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for fractions of a penny
Eric,
Also suggest you review this article... http://www.cpearson.com/excel/rounding.htm Regards, Jim Cone San Francisco, USA "Wescotte" wrote in message ups.com... I thought of that but I need to keep the correct value in the cell and just not let the user advance until they correct it manually because if I just round everything it will cause a headache trying to figure out where your extra penny went/came from. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for fractions of a penny
Hi,
how about this: If CStr(CLng(.Value * 100) / 100) < CStr(.Value) Then -- HTH okaizawa Wescotte wrote: I'm attempting to write code to determine if the user entered a value that has a fraction of a cent. Here is my logic dim i as long dim d as double If IsNumeric(.Value) = True And .Value < 0 Then .Value = Abs(.Value) i = CLng(CDbl(.Value) * 100) d = CDbl(.Value) * 100 If Abs(d - i) 0 Then MsgBox "Rounding error detected! Make sure you don't have umbers with values less than 1/100th", vbExclamation End If Else .Value = "" End If It seems to work for most cases however I'd found some specific values that always report "Round error detected!...." Values such as 282.47, 32.41 and 132.70 If I MsgBox i & " " & d the numbers are the same so Abs(d - i) 0 should always be false I don't quite understand what's doing on. Is there a better way to test for fractions of a cent? Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calc sometimes off by a penny | Excel Worksheet Functions | |||
Exel 2007 penny off | Excel Discussion (Misc queries) | |||
AutoSum lost a penny | Excel Worksheet Functions | |||
My accounting program is a penny off, Why? | Excel Worksheet Functions | |||
In Excel the total is off by a penny. | Excel Worksheet Functions |