All –

Could some exceedingly kind wizard please run this very simple code snipit, and then explain it (the results) to me? Repeatable on my home machine and work machine running XL2002. On my Mac XL2004, I don’t have this problem.

Sub Test()

Const Max_P As Currency = 2000000

Dim P As Currency

Dim K As Long

For P = 12 To Max_P Step 2

Next P

For K = 12 To Max_P Step 2

Next K

Debug.Print P; K

End Sub

Const Max_P As Currency = 2000000

Dim P As Currency

Dim K As Long

For P = 12 To Max_P Step 2

Next P

For K = 12 To Max_P Step 2

Next K

Debug.Print P; K

End Sub

For P, I get 1717988 (???????)

For K, I get 2000002

And, well, I don’t get what I get ;-)

…mrt

Hmm, bit weird that.

I note that defining Max_P as 4000000 and starting at 2000012 gives the expected result, and that the data type of Max_P doesn’t seem to matter.

Call Grace Hopper!

Weirdness confirmed. (But why are you using Currency when you could be using Long?).

XL2007 gets 1717988 and 2000002

dbb –

Thanks. I was using currency to be able to hold 100000^2. It has to do with Euler 75, next Sunday’s problem. I was confident of my approach, but just wasn’t getting the right answer. I found a website that gave the last triangle with perimeter below 2000000, and I couldn’t get that answer out from inside the loop above. Tracking that down lead me to P not getting to 2000000. I’ve only been up against this weirdness since mid-last week. If I’d cracked it earlier, #75 would have been up this week.

From there, I adjusted my types, and got the answer. Using currency only where I needed it.

Can this be considered a bug ;-)

Certainly was to me.

…mrt

1,717,986.9183

If the first 34 bits are ‘on’, this is the value. If it was ETC, I think it would crap out at the 33rd bit. Strange.

The lowest value of max_p where the equality P=K fails is 214748. Strange things happen at, near, and between higher multiples of 214748.

The prime factorization of 214748 is 2^2 x 37 x 1,451, which seems like a lead because it is so “primey”… maybe this has to do with conversion of decimal to binary? Odd though, because the Currency type should not lose accuracy on whole numbers.

Strange. In XL2003 if Max_P is defined as 1932734 or 1932735 I get 2147484 and 1932736. 2932739 gives 2576982 and 2932740.

Going back to Max_P = 2000000 but starting the loop at 1900000 gives 1900000 and 2000002.

It’s start going wrong for max_P values of 1932734 and higher (using XL2007 on winXPProf)

From VB6 Language Reference Book 1 p1153

The Currency data type is useful for calculations involving money and for fixed-point calculations in which accuracy is particularly important.

I don’t know about the Currency/Long thing (same results on Win 2002 btw) but I think you may be able to get around some of the big squaring stuff on Pythagorean triangles by looking at some of the properties described here: http://en.wikipedia.org/wiki/Pythagorean_triple – for example, that one of the two orthogonal sides is always divisible by 3. There are a whole bunch more. So the brute force solution need not be quite so, er, brutal!

Have you solved #73, btw? I’m stuck on what it tells me is a wrong answer, but I can’t figure out for the life of me what I’ve missed.

To achieve the desired result, you can possibly use te forgotten Decimal data subtype.

Following code does the job: (XL2007, Win Vista)

Const Max_P As Variant = 2000000

Dim P As Variant

For P = 12 To CDec(Max_P) Step 2

Next P

Debug.Print P

End Sub

Interesting. Sounds like a bug to me indeed.

Funny thing is, if you add a counter in the first loop, you get another odd result:

Const Max_P As Currency = 2000000

Dim P As Currency

Dim K As Long

Dim u As Long

For P = 12 To Max_P Step 2

u = u + 2

Next P

For K = 12 To Max_P Step 2

Next K

Debug.Print P; K; u

End Sub

Result:

1717988 2000002 1717976

Where did the 12 go?

OK, found it, the loop starts at 12, silly me.

and take a look at

This gives the expected results ( 2000002 2000002 ):

Const Max_P As Currency = 2000000

Dim P As Currency

Dim K As Long

P = 12

Do

P = P + 2

If P > Max_P Then Exit Do

Loop

K = 12

Do

K = K + 2

If K > Max_P Then Exit Do

Loop

Debug.Print P; K

End Sub

You *definitely* don’t want to be trying brute force on Euler #75, by the way.

Look at Euclid’s theorem on the Wikipedia page and think about the problem from a slightly different angle. You shouldn’t need square a number much larger than 1000.

I solved it in Ruby in about 42sec, which makes me think I might have missed a trick somewhere as it ran on a very fast machine. I haven’t looked at the discussion yet, so I don’t know.

Hi Mike –

#75 took me a bloody week and a half because of the above issue to solve. I was only tallying singletons thru 1717986 and didn’t have a clue until a website gave me the last triangle, and that loop above wouldn’t printout the number of times that length perimeter was found.

Now, using longs, it comes in at about 1.5 seconds on my slower machine.

Interestingly, I was using currency to store precomputed sides squared up to 100000^2 for a verification step I DIDN’T NEED TO MAKE. (If Squared(a) + Squared(b) = Squared(c) then…. ) It never filtered anything.

If I hadn’t been doing that, I’d never have encountered this.

Might be another case where VBA wins ;-) If I get the time tonight, I’ll put it up. As Desi says, there some ‘splaining to do.

…mrt

i was getting the same result (XL2007)… but now i get the correct answer… do not know what i changed. here is what i have…

Sub Test2()

Dim Max_P, P, u As Currency

Dim K, v As Long

Max_P = 2000000

u = 0

v = 0

For P = 12 To Max_P Step 2

u = u + 1

Next P

For K = 12 To Max_P Step 2

v = v + 1

Next K

MsgBox P & “, ” & K

End Sub

jvc –

Return Max_P to a Constant, it gives same bad result

Interesting discovery though…

…mrt

jvc –

When you put multiple variables in a single Dim, it only sets the type to the last variable. So P is set to a variant.

[…] You can’t count on money Posted on February 28, 2009 by dougaj4 An interesting bug is reported at Daily Dose of Excel: Code snipit to test […]

Some screen shots showing more strange behaviour when using the currency data type as a counter here: http://newtonexcelbach.wordpress.com/2009/02/28/you-cant-count-on-money/

This bug is a bit of a worry in my opnion, not so much because of the results of using currency as a counter, but more because similar unexpected behaviour might be hidden away in calculations involving this data type. I understand that there are quite a few people who use Excel for just this purpose.

For big counters the solution is simple, just use a double, or as brv said, for really huge counters you could use a decimal.

I might have a look at using the decimal data type on some of the Project Euler problems (although I suspect there are always more elegant solutions using other means).

No one here knows that a Control-click on the Mac is the same as a right click on a PC?