When is an integer not an integer?

[JenniferMurphy]

This is probably obvious, but not to me.

I am trying to write a little UDF to convert a probability (0-1) into a fraction (ratio). For example, 25% would become "3/1", 40% would become "1.5/1", etc.

I have that working. Now I want to convert any fractions that are not integers in the numerator to another fraction that is. For example. 40% would become "3/2" instead of "1.5/1". To my addled brain, the code below should work. It even looks like it is working, but it is not. What I am doing is multiplying that numerator (1.5) by the integers from 1 to 10. If any of those results are an integer, I return that value with the multiplier (denom) as the denominator.

In the example below, the UDF is passed "0.4". It converts that to "1.5" (frac). On the second pass through the For loop (denom = 2), the numerator (numer) appears to be "3", but it fails the test against Int(numer). In the Immedite window, numer is "3", but int(numer) is "2".

Can someone tell me what is going on?

 

[JenniferMurphy]

I tried replacing the Int function with Fix and then Round. Fix had the same result as Int. Round appeared to get a different result. In the Immediate window, both Int and Fix return "2", whereas Round returns "3", but it gets False when compared to numer.

What is going on here?

 

[JenniferMurphy]

I changed all of the variables to type Variant and now it works. Why is that?

 

[JenniferMurphy]

Ooops, spoke too soon. It fails if the percentage passed is 0.75. According to the Immediate window, both numer and round(numer,0) equal 1, but they do not equal each other.

 

[Marius44]

Hello
I believe the problem is in this line of code:
frac = (1 / pPC) - 1

Why do you put that - 1?

25% is equal to 1/4 and not 1/3
Or the problem is not clear to me.

Hello,
Mario

 

[Alex Blakenburg]

Will this work for you ?

If Round(numer, 15) = Round(numer, 0) Then Exit For

 

[JenniferMurphy]

Will this work for you ?

If Round(numer, 15) = Round(numer, 0) Then Exit For

You did it again. Thank you, thank you, thank you.

That seems to be working. I think I understand why it works, but can you tell me why my code fails?

 

[JenniferMurphy]

Hello
I believe the problem is in this line of code:
frac = (1 / pPC) - 1

Why do you put that - 1?

25% is equal to 1/4 and not 1/3
Or the problem is not clear to me.

Hello,
Mario

Yes, 25% (0.25) = 1/4, but that is not the type of "fraction" that I am converting to. This code is intended to convert the probability into the fractional betting odds notation used in the UK. For that, 25% is 3/1 odds.

 

[Alex Blakenburg]

That seems to be working. I think I understand why it works, but can you tell me why my code fails?

I think it is just to do with the floating point precision issue.
I tried using "if numer - Round(numer,0) = 0 then" --> which also showed false
and then used debug.print numer - Round(numer,0) and got a difference to the power of -16.
I don't know if that relates to the fact that at the xml level the precision is actually 17 digits (ie more than the Excel 15 digits)

Using my suggested If statement I tried setting the Dim statements back to Double and that also worked fine.

 

[JenniferMurphy]

I think it is just to do with the floating point precision issue.
I tried using "if numer - Round(numer,0) = 0 then" --> which also showed false
and then used debug.print numer - Round(numer,0) and got a difference to the power of -16.
I don't know if that relates to the fact that at the xml level the precision is actually 17 digits (ie more than the Excel 15 digits)

I had an idea that seems to work. Since numer looks like an integer, what if I convert it to text and look for a decimal point?

This code seems to work:

'Search for an integer numerator for each integer denominator
For denom = 1 To denommax     'Loop thru the denominators
  numer = frac * denom          'Calculate the numerator
  'If it's an integer, return it
  If 0 = InStr(1, numer & " ", ".") Then Exit For
Next denom

Comments?