Just for the fun of it

[Mollethewizard]

I suppose all of us that read this forum are working people with working problems. Sometimes I for one just want to play with something and solve a problem that’s not work related, so just for the fun of it….

If you take a number lets say 3 and you divide it by 2 and multiply by 3 and add 1 if the result of the division is not even you end up by 1.

You divide it by 2 the result 1.5 is not an even number.

We multiply 3 by 3 and add 1 the result 10 we divide by 2 (5) and then again by 2 (2.5) – not an even number. We multiply 5 by 3 and add 1 (16) divide by 2 (8) divide by 2 (4) divide by 2 (2) divide by 2 (2) divide by 2 and ends up with 1 in 8 stages!

How do you write code in Excel to present the original number 3 in cell A1 and the flow of numbers by the calculations (3, 10, 5, 16, 8, 4, 2, 1) in column B starting from cell B1?

A good number to test with is 253 it gives a fairly long follow of operations!

Mollethewizard

 

[TonyJollans]

Hi Mollethewizard,

I suspect many of us here wish we were working people with working problems

I'm not sure your description is quite the same as your example but all you need is a simple formula in each cell in row 1 (except A1 of course). Here's what goes in A2, copy across to the end.

=IF(OR(A1="",A1=1),"",IF(MOD(A1,2)=0,A1/2,A1*3+1))

Enjoy,
Tony

 

[Mollethewizard]

Not quite what I had in mind….

I meant VBA code that presents each stage in the calculation process (the results of divisions and multiplications by 3 + 1).

By your answer – are you unemployed or was it an irony?

Mollethewizard

 

[TonyJollans]

Hi Mollethewizard,

'fraid I am unemployed but here is not the place to gripe about it - I enjoy it here. Can't do it right now - I have to go and sign on (are you English - do you know what that means?). If no-one else does it first I'll post some code later.

Enjoy,
Tony

 

[Mollethewizard]

Tony!

Sorry to hear that.

Mollethewizard – nighter English or English spoken but I still know what sign on is.

 

[xlbo]

I'm sure someone'll come up with something prettier but this should work:

Sub GetCalcFlow()
Dim nVal As Long, ctr As Integer
nVal = Range("A1&quot.Value
ctr = 1
Do While nVal <> 1
If Not Int(nVal / 2) = nVal / 2 Then
nVal = (nVal * 3) + 1
Range(&quot;B&quot; & ctr).Value = nVal
ctr = ctr + 1
Else
nVal = nVal / 2
Range(&quot;B&quot; & ctr).Value = nVal
ctr = ctr + 1
End If
Loop
End Sub

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[Mollethewizard]

Geoff!

The code works just like a dream!

As for the prettiness of VBA-code, is there something as pretty code? I thought prettiness was reserved for women….

Mollethewizard

 

[xlbo]

Yeh - maybe I spend too much time lookin at my screen ;-)

I just think that the &quot;test&quot; could be better - rather than using INT and that I shouldn't really need to have 2 instances of :
Range(&quot;B&quot; & ctr).Value = nVal
ctr = ctr + 1

Here we go - this is a bit nicer:

Sub GetCalcFlow()
Dim nVal As Double, ctr As Integer
nVal = [A1]
ctr = 1
Do While nVal <> 1
If Not Int(nVal / 2) = nVal / 2 Then
nVal = (nVal * 3) + 1
Else
nVal = nVal / 2
End If
Cells(ctr, 2) = nVal
ctr = ctr + 1
Loop
End Sub

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[TonyJollans]

Hi Molle, Geoff,

I probably spend too much time loking at my screen too, but I tend to think that if your code isn't neat, your logic probably isn't either.

It is personal preference but I generally find MOD a neater check than INT, so instead of ..

If Not Int(nVal / 2) = nVal / 2

.. I would use ..

If nVal Mod 2 > 0

Enjoy,
Tony

 

[xlbo]

That's the kinda thing I was looking for - I always forget about Mod. Knew there was something that'd look shorter and neater....and that was it ;-)
I know how it works but I'm always confused by the results - 10 Mod 3 = 1 ????
I just don't get how 10/3 leaves a remainder of 1 ???
(and yes I did do A level matchs....I just can't remember any of it )

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[Mollethewizard]

Okay talking about prettiness – Geoff’s first code a woman without makeup the second the same woman with perfume and makeup.

Who will come up with the bikini-version of the same woman?

Mollethewizard

 

[xlbo]

brain spazm - just remembered how MOD works

Dunno if there's any more that can be done to this.....maybe it's just not a good idea to see a bikini clad version of this code ;-)

Sub GetCalcFlow()
Dim nVal As Double, ctr As Integer
nVal = [A1]
ctr = 1
Do While nVal <> 1
Select case nVal Mod 2
case is >0
nVal = (nVal * 3) + 1
case else
nVal = nVal / 2
end select
Cells(ctr, 2) = nVal
ctr = ctr + 1
Loop
End Sub

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[strongm]

Possibly not a bikini, but here's another minor reworking:

Sub GetCalcFlow()
Dim nVal As Double, ctr As Integer
nVal = [A1]
ctr = 1
Do
nVal = IIf(nVal Mod 2, (nVal * 3) + 1, nVal / 2)
Cells(ctr, 2) = nVal
ctr = ctr + 1
Loop Until nVal = 1
End Sub