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)

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

produced .21 for me.

--
Regards,
Tom Ogilvy

"Paul W Smith" wrote in message
...
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)

On Sat, 7 Jan 2006 23:47:24 -0000, "Paul W Smith"
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

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" wrote in message
...
On Sat, 7 Jan 2006 23:47:24 -0000, "Paul W Smith"
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

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"
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" wrote in message
.. .
On Sat, 7 Jan 2006 23:47:24 -0000, "Paul W Smith"
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

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" wrote in message
...
On Sat, 7 Jan 2006 23:47:24 -0000, "Paul W Smith"
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