Floating Point Problem
am 02.11.2007 12:50:06 von Hansen
Hi
I have a report and I am doing the following calculation in the load
report event:
Me.TbxClass1_1.Value = Sales1Count \ Me.TbxSales1.Value
where Me.TbxSales1.Value gets determined by: (Starts out as 0)
Me.TbxSales1.Value = Me.TbxSales1.Value + CDbl(Rst!RSales) and has a
value of 159
and Sales1Count gets determined by: (Starts out as 0)
Sales1Count = Sales1Count + CDbl(Rst!RSales) and has a value of 149
I have tried to declare Sales1Count as Double, single, variant, etc
but Me.TbxClass1_1.Value is 0. If i multiply Sales1Count by 100, I get
93. The answer should be 0.93710691823899371069182389937107. How do I
get this value and where am I going wrong?
Re: Floating Point Problem
am 02.11.2007 13:17:19 von lyle
On Nov 2, 7:50 am, Hansen wrote:
> Hi
>
> I have a report and I am doing the following calculation in the load
> report event:
> Me.TbxClass1_1.Value = Sales1Count \ Me.TbxSales1.Value
>
> where Me.TbxSales1.Value gets determined by: (Starts out as 0)
> Me.TbxSales1.Value = Me.TbxSales1.Value + CDbl(Rst!RSales) and has a
> value of 159
>
> and Sales1Count gets determined by: (Starts out as 0)
> Sales1Count = Sales1Count + CDbl(Rst!RSales) and has a value of 149
>
> I have tried to declare Sales1Count as Double, single, variant, etc
> but Me.TbxClass1_1.Value is 0. If i multiply Sales1Count by 100, I get
> 93. The answer should be 0.93710691823899371069182389937107. How do I
> get this value and where am I going wrong?
I think this will get it to 28 digits after the decimal.
Public Function DecimalQuotient(ByVal divisor#, dividend#)
DecimalQuotient = CDec(divisor) / CDec(dividend)
End Function
Sub test()
Debug.Print DecimalQuotient(149, 159)
'0.9371069182389937106918238994
End Sub
But you want it to 46 digits. Hmmmm!
If I really needed more than 28 I guess I would look at automating the
Windows Calculator (don't know if this is possible), building a dll
with some application that handled decimal representation of numbers
to such accuracy, or building my own division procedure (YIKES!).
But I can't image why I would need this; I have found that it can help
with accuracy accuracy to avoid the conversion of numbers like 149/159
to their decimal representation and just to use 149/159 in any
calculation.
Re: Floating Point Problem
am 02.11.2007 13:41:42 von Allen Browne
FYI, \ is a valid operator too, but works differently than /.
\ performs integer division. Any remainder is discarded.
So you get 2 as the result of:
8 \ 3
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Hansen" wrote in message
news:1194006670.573909.217330@y42g2000hsy.googlegroups.com.. .
> Hi Lyle
>
> Thanks for your answer. It works well. But when reading and applying
> your code I noticed what I had been doing wrong. Division is / not \.
> Just goes to show you. You can change world but for a typo!
>
Re: Floating Point Problem
am 04.11.2007 07:02:54 von CDMAPoster
On Nov 2, 7:50 am, Hansen wrote:
> Hi
>
> I have a report and I am doing the following calculation in the load
> report event:
> Me.TbxClass1_1.Value = Sales1Count \ Me.TbxSales1.Value
>
> where Me.TbxSales1.Value gets determined by: (Starts out as 0)
> Me.TbxSales1.Value = Me.TbxSales1.Value + CDbl(Rst!RSales) and has a
> value of 159
>
> and Sales1Count gets determined by: (Starts out as 0)
> Sales1Count = Sales1Count + CDbl(Rst!RSales) and has a value of 149
>
> I have tried to declare Sales1Count as Double, single, variant, etc
> but Me.TbxClass1_1.Value is 0. If i multiply Sales1Count by 100, I get
> 93. The answer should be 0.93710691823899371069182389937107. How do I
> get this value and where am I going wrong?
For those who aren't satisfied with the 28 or so digits past the
decimal point of their calculator program I have written a function.
As long as the numerator and denominator don't go past about eight or
nine digits and aren't zero or negative, it should give correct
results. Please post back if anyone discovers any situations that I
have not accounted for. I'm thinking about doing a more general
version using strings that doesn't have those limitations, but that's
a little more challenging.
'---Begin Module Code---
Public Function Quotient(lngNumer As Long, lngDenom As Long, intPlaces
As Integer) As String
Dim intDecimalPoint As Integer
Dim intNextDecimal As Integer
Dim lngSubtrahend As Long
Dim strTemp As String
intDecimalPoint = Int((Log(CDbl(lngNumer) / lngDenom)) / Log(10)) + 1
strTemp = ""
If intDecimalPoint <= 0 Then
strTemp = "0." & String(Abs(intDecimalPoint), "0")
Do While lngNumer < lngDenom
lngNumer = lngNumer * 10
Loop
Else
If lngNumer > lngDenom Then
strTemp = CStr(lngNumer \ lngDenom) & "."
lngNumer = (lngNumer Mod lngDenom) * 10
End If
End If
Do While Len(strTemp) - InStr(1, strTemp, ".") < intPlaces
lngSubtrahend = 0
If lngNumer < lngDenom Then
intNextDecimal = 0
Else
intNextDecimal = CInt(Left(CStr(CDbl(lngNumer) \ lngDenom), 1))
lngSubtrahend = intNextDecimal * lngDenom
End If
strTemp = strTemp & CStr(intNextDecimal)
If lngNumer - lngSubtrahend < lngDenom Then
lngNumer = (lngNumer - lngSubtrahend) * 10
Else
lngNumer = lngNumer - lngSubtrahend
End If
Loop
Quotient = strTemp
End Function
'---End Module Code---
Sample calls and results:
MsgBox(Quotient(1235, 238976, 60)) =>
0.0051678829673272629887520085698982324584895554365291912158 54
MsgBox(Quotient(238976, 1235, 60)) =>
193.50283400809716599190283400809716599190283400809716599190 2834
MsgBox(Quotient(1, 123456789, 40)) =>
0.0000000081000000737100006707610061039251
MsgBox(Quotiend(149, 159, 100)) =>
0.9371069182389937106918238993710691823899371069182389937106 918238993710691823899371069182389937106918
Note that the function chops after the selected number of digits
rather than rounding. Maybe I should also write a Round function for
those kinds of strings. It looks like there are lots of ways to
optimize the Quotient function. Thanks for asking the question.
James A. Fortune
CDMAPoster@FortuneJames.com