Rounding

[Paul W Smith]

Resouce A
16.0 = 16 big units or 96 small units
16.4 = 16 big units and 4 small units, total 100 small units

I am trying to find some averages, and having troubles!.

If 21 wigets cost 16.4 (100 small units), simple math tells me that each
small unit = 0.21 wigets. However Excel, calculating with VBA I get
2.0999999905 - at least that is what I get when I apply result onto my
worksheet.

I am using:

widgets / (Int(Resouce A) * 6 + (Resouce A - Int(Resouce A)) * 10)

[Tom Ogilvy]

=21/(INT(A1) * 6 + (A1 - INT(A1)) * 10)

produced .21 for me.

--
Regards,
Tom Ogilvy

"Paul W Smith" <[email protected]> wrote in message
news:[email protected]...
> Resouce A
> 16.0 = 16 big units or 96 small units
> 16.4 = 16 big units and 4 small units, total 100 small units
>
> I am trying to find some averages, and having troubles!.
>
> If 21 wigets cost 16.4 (100 small units), simple math tells me that each
> small unit = 0.21 wigets. However Excel, calculating with VBA I get
> 2.0999999905 - at least that is what I get when I apply result onto my
> worksheet.
>
> I am using:
>
> widgets / (Int(Resouce A) * 6 + (Resouce A - Int(Resouce A)) * 10)
>
>

[Ron Rosenfeld]

On Sat, 7 Jan 2006 23:47:24 -0000, "Paul W Smith" <[email protected]>
wrote:

>Resouce A
>16.0 = 16 big units or 96 small units
>16.4 = 16 big units and 4 small units, total 100 small units
>
>I am trying to find some averages, and having troubles!.
>
>If 21 wigets cost 16.4 (100 small units), simple math tells me that each
>small unit = 0.21 wigets. However Excel, calculating with VBA I get
>2.0999999905 - at least that is what I get when I apply result onto my
>worksheet.
>
>I am using:
>
>widgets / (Int(Resouce A) * 6 + (Resouce A - Int(Resouce A)) * 10)
>

I don't know what "apply result onto my worksheet" means ???

But I get 0.21 with VBA using *your* formula. So the problem lies elsewhere.
Maybe in the "apply result onto my worksheet step", or maybe in something else
that you have not shared with us thinking it irrelevant.

====================
sub foo()
Dim CostPerWidget As Double
Const ResourceA As Double = 16.4
Const Widgets As Double = 21

CostPerWidget = Widgets / (Int(ResourceA) * 6 _
+ (ResourceA - Int(ResourceA)) * 10)
Debug.Print CostPerWidget

End Sub
=======================
0.21
======================


--ron

[Paul W Smith]

I am having to use a complicated user function to 'apply the result to my
worksheet'.

A simplified version is:

Public Function UserFunc(Widgets As Integer, ResourceA As Single)
UserFunc = Widgets / (Int(ResourceA) * 6 + (ResourceA - Int(ResourceA))
* 10)
End Function


if you use =UserFunc(A1, A2), where cells A1 = 21, A2 = 16.4, you will see
my problem - what you get on the worksheet is 0.21000001



"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Sat, 7 Jan 2006 23:47:24 -0000, "Paul W Smith" <[email protected]>
> wrote:
>
>>Resouce A
>>16.0 = 16 big units or 96 small units
>>16.4 = 16 big units and 4 small units, total 100 small units
>>
>>I am trying to find some averages, and having troubles!.
>>
>>If 21 wigets cost 16.4 (100 small units), simple math tells me that each
>>small unit = 0.21 wigets. However Excel, calculating with VBA I get
>>2.0999999905 - at least that is what I get when I apply result onto my
>>worksheet.
>>
>>I am using:
>>
>>widgets / (Int(Resouce A) * 6 + (Resouce A - Int(Resouce A)) * 10)
>>
>
> I don't know what "apply result onto my worksheet" means ???
>
> But I get 0.21 with VBA using *your* formula. So the problem lies
> elsewhere.
> Maybe in the "apply result onto my worksheet step", or maybe in something
> else
> that you have not shared with us thinking it irrelevant.
>
> ====================
> sub foo()
> Dim CostPerWidget As Double
> Const ResourceA As Double = 16.4
> Const Widgets As Double = 21
>
> CostPerWidget = Widgets / (Int(ResourceA) * 6 _
> + (ResourceA - Int(ResourceA)) * 10)
> Debug.Print CostPerWidget
>
> End Sub
> =======================
> 0.21
> ======================
>
>
> --ron

[Ron Rosenfeld]

Actually: 0.210000008344650

Others can explain the reasons better than I. But if you declare your variables
(at least the ResourceA variable) as Double, you will obtain the answer you
desire.

The reasons have to do with IEEE standards, expressing base 10 numbers in
binary, and how VBA does math.

An alternative would be to round the result to the significant number of digits
within your UDF.

--ron




On Sun, 8 Jan 2006 01:07:11 -0000, "Paul W Smith" <[email protected]>
wrote:

>I am having to use a complicated user function to 'apply the result to my
>worksheet'.
>
>A simplified version is:
>
>Public Function UserFunc(Widgets As Integer, ResourceA As Single)
> UserFunc = Widgets / (Int(ResourceA) * 6 + (ResourceA - Int(ResourceA))
>* 10)
>End Function
>
>
>if you use =UserFunc(A1, A2), where cells A1 = 21, A2 = 16.4, you will see
>my problem - what you get on the worksheet is 0.21000001
>
>
>
>"Ron Rosenfeld" <[email protected]> wrote in message
>news:[email protected]...
>> On Sat, 7 Jan 2006 23:47:24 -0000, "Paul W Smith" <[email protected]>
>> wrote:
>>
>>>Resouce A
>>>16.0 = 16 big units or 96 small units
>>>16.4 = 16 big units and 4 small units, total 100 small units
>>>
>>>I am trying to find some averages, and having troubles!.
>>>
>>>If 21 wigets cost 16.4 (100 small units), simple math tells me that each
>>>small unit = 0.21 wigets. However Excel, calculating with VBA I get
>>>2.0999999905 - at least that is what I get when I apply result onto my
>>>worksheet.
>>>
>>>I am using:
>>>
>>>widgets / (Int(Resouce A) * 6 + (Resouce A - Int(Resouce A)) * 10)
>>>
>>
>> I don't know what "apply result onto my worksheet" means ???
>>
>> But I get 0.21 with VBA using *your* formula. So the problem lies
>> elsewhere.
>> Maybe in the "apply result onto my worksheet step", or maybe in something
>> else
>> that you have not shared with us thinking it irrelevant.
>>
>> ====================
>> sub foo()
>> Dim CostPerWidget As Double
>> Const ResourceA As Double = 16.4
>> Const Widgets As Double = 21
>>
>> CostPerWidget = Widgets / (Int(ResourceA) * 6 _
>> + (ResourceA - Int(ResourceA)) * 10)
>> Debug.Print CostPerWidget
>>
>> End Sub
>> =======================
>> 0.21
>> ======================
>>
>>
>> --ron
>

--ron

[Jerry W. Lewis]

Most terminating decimal fractions are nonterminating binary fractions that
can only be approximated in binary. When you do math with approximate
inputs, you should not be surprised if the output is only approximately
correct.

However, you do have some control over the degree of approximation.
If you declare ResourceA As Single, then it only carries 7 figure accuracy ...
If you declare ResourceA As Double, then it carries 15 figure accuracy.

Try the following simple function to see that by declaring it Single you
were using 16.3999996185302 = check1(16.4) in you calculation

Function check1(x As Single) As Double
check1 = x
End Function

If you want to learn more, you might find my functions at
http://groups.google.com/group/micro...06871cf92f8465
to be useful.

Jerry

"Paul W Smith" wrote:

> I am having to use a complicated user function to 'apply the result to my
> worksheet'.
>
> A simplified version is:
>
> Public Function UserFunc(Widgets As Integer, ResourceA As Single)
> UserFunc = Widgets / (Int(ResourceA) * 6 + (ResourceA - Int(ResourceA))
> * 10)
> End Function
>
>
> if you use =UserFunc(A1, A2), where cells A1 = 21, A2 = 16.4, you will see
> my problem - what you get on the worksheet is 0.21000001
>
>
>
> "Ron Rosenfeld" <[email protected]> wrote in message
> news:[email protected]...
> > On Sat, 7 Jan 2006 23:47:24 -0000, "Paul W Smith" <[email protected]>
> > wrote:
> >
> >>Resouce A
> >>16.0 = 16 big units or 96 small units
> >>16.4 = 16 big units and 4 small units, total 100 small units
> >>
> >>I am trying to find some averages, and having troubles!.
> >>
> >>If 21 wigets cost 16.4 (100 small units), simple math tells me that each
> >>small unit = 0.21 wigets. However Excel, calculating with VBA I get
> >>2.0999999905 - at least that is what I get when I apply result onto my
> >>worksheet.
> >>
> >>I am using:
> >>
> >>widgets / (Int(Resouce A) * 6 + (Resouce A - Int(Resouce A)) * 10)
> >>
> >
> > I don't know what "apply result onto my worksheet" means ???
> >
> > But I get 0.21 with VBA using *your* formula. So the problem lies
> > elsewhere.
> > Maybe in the "apply result onto my worksheet step", or maybe in something
> > else
> > that you have not shared with us thinking it irrelevant.
> >
> > ====================
> > sub foo()
> > Dim CostPerWidget As Double
> > Const ResourceA As Double = 16.4
> > Const Widgets As Double = 21
> >
> > CostPerWidget = Widgets / (Int(ResourceA) * 6 _
> > + (ResourceA - Int(ResourceA)) * 10)
> > Debug.Print CostPerWidget
> >
> > End Sub
> > =======================
> > 0.21
> > ======================
> >
> >
> > --ron
>
>
>