formulas for counting in bases other than 10

[youbet7469]

Dear group... Can anyone tell me how to program formulas into excel to count in bases other than 10. (i.e. base six would be 1,2,3,4,5,6,11,12,13,14,15,16,21)

Ideally I'd like to be able to work with all the different bases up through 22..

Any advice would be greatly appreciated.

Thanks

Greg

[Ivan F Moala]

On 2002-04-08 20:48, youbet7469 wrote:
Dear group... Can anyone tell me how to program formulas into excel to count in bases other than 10. (i.e. base six would be 1,2,3,4,5,6,11,12,13,14,15,16,21)

Ideally I'd like to be able to work with all the different bases up through 22..

Any advice would be greatly appreciated.

Thanks

Greg

One way is to use this UDF
Press Alt F11
Click > Insert > Module
Paste this code in.

Function BaseConv(InputNum, BaseNum)

Dim Quotient, Remainder As Single

Dim Answer As String



Quotient = InputNum   ' Set quotient to number to convert.

Remainder = InputNum  ' Set remainder to number to convert.

Answer = ""



Do While Quotient <> 0   ' Loop while quotient is not zero.

    ' Store the remainder of the quotient divided by base number in a

    ' variable called remainder.

    Remainder = Quotient Mod BaseNum

    ' Reset quotient variable to the integer value of the quotient

    ' divided by base number.

    Quotient = Int(Quotient / BaseNum)

    ' Reset answer to contain remainder and the previous answer.

    Answer = Remainder & Answer

    ' Convert answer variable to a number.

Loop

BaseConv = Val(Answer)

End Function

Just refernce it as a Std formula in your
workbook As

=BaseConv(256,19)

Just a word of caution...it uses loops which
can be a little slow depending on the base
and the number of formulas you have in your
worksheet....

_________________
Kind Regards,
Ivan F Moala
http://<font color="green"><a href="...r Construction

[ This Message was edited by: Ivan F Moala on 2002-04-08 21:24 ]

[Ivan F Moala]

Woops....sorry that CONVERTS from Base10
to your base......sorry....will have another
play...

[youbet7469]

Ivan, you lost me.... I don't have any experience programming in Visual Basic... Is there any way to do this just within excel? If not ...I'm game for learning the VB that needs to be learned, its just a matter of whats the quickest and easiest

Thank you for your assistance

Greg

[Chris Davison]

Evening all,

would this be the same as the =MOD function ?

(I dont know, hence the question)

[Mark O'Brien]

It's not the same as the MOD function.

The only built in bases that excel have are the fairly standard ones.

DEC, OCT, BIN and HEX.

If you want to use the other bases, you'll almost certainly have to use VBA.

To convert from base 6 to decimal I guess you would have to do something like this in VBA:

Base 6 = 666
Dec = 6(6^2) + 6(6^1) + 6(6^0)

Which may actually be a reasonably easy thing to code generically in VBA. I may give this a shot tonight.

[Damon Ostrander]

Hi YouBet7469,

Here is a UDF that converts an Excel number to any base from 2 up to 37. Enjoy.


Function ToBase(ByVal X As Double, Base As Integer) As String

'Converts a number X to any base from 2 up to 37. The result is
'a string value. For bases larger than 10, digits 10, 11, 12, etc.,
'are represented A, B, C, etc., as is traditional for representing
'hexadecimal numbers.

'Example: =ToBase(34,12) yields "2A", or 2 x 12 + 10

Dim Digit As Integer
Dim K As Integer
Dim i As Integer
If X > 0 Then
K = Int(Log(X) / Log(Base))
For i = K To 0 Step -1
Digit = X Base ^ i
If Digit < 10 Then
ToBase = ToBase & CStr(Digit)
Else
ToBase = ToBase & Chr(Digit + 55)
End If
X = X - Digit * Base ^ i
Next i
Else
ToBase = "0"
End If

End Function

[youbet7469]

Gentlemen, thanks for your help.... any suggestions as to where to go to learn how to enter the above formulas in using Visual Basic? I'm a complete newbie when it comes to working with that.

I really appreciate your input and assistance.

Best Regards,
Greg

[youbet7469]

Hey Guys, Me again...

I figured out a little of what one needs to do in VB to get it into excel.. I've run into a couple of things hoping you can give me a little guidance.

1. When I first cut and pasted the formula that Damon gave for the bases in VB I got a message that there was an error in the formula. It highlighted the line that reads

Digit = X \ Base ^ i

What I did was delete one of the "" figuring that it was just a typo in the formula.. Is that correct.

2. After doing some quick programing I am wondering if there might be an error in the formula. I am not that familiar with counting in bases other than 10 so I might be wrong, please feel free to correct me if you know for sure, but for example it was my understanding that when counting in base 5 you would count as follows:

1,2,3,4,5,11,12,13,14,15,21,22,23,24,25,31...

The formula u gave me gives me the following for base 5 counting

1,2,3,4,5,10,11,12,13,14,15,20,21,22,23,24,25,30,31

Another example is counting in base 9

My understanding of base 9 counting is that it should count as follows:

1,2,3,4,5,6,7,8,9,11,12,13,14,15,16,17,18,19,21,22,23,24,25,26,27,28,29,31

The formula that u gave me gives the following for base 9 counting:

1,2,3,4,5,6,7,8,9,11,12,13,14,15,16,17,18,20,21,22,23,24,25,26,27,28,30

Is my understanding of counting in different bases wrong or is it possible that there is a slight "glitch" in the formula?

Thanks again for your assistance, it is greatly appreciated!

Best Regards,
Greg

[Jay Petrulis]

Hi Greg,

Base 5 numbers are from 0-4, so you can never have a 5 as a digit. Base 9 goes from 0-8 so you can never have a 9 in the result.

When testing converting from base 10 to base 2-9, Damon's formula returns the correct results for me, except for specific cases in base 3 and base 9.

In base 3, the exponents of 3 return incorrect results

3^1 = 3, should be 10
3^2 = 30 should be 100
3^3 = 300 should be 1000
.
.
.

9^1 = 9, should be 10
9^2 = 90, should be 100
9^3 = 900, should be 1000
.
.
.

The formula returns the correct result for *ALL* other values (didn't check 11-37).

I tested Damon's formula vs. a formula posted by Leo Heuser, which is limited to base 2-9 conversion.

--------------------
Function Convert(Number As Long, NumberSystem As Integer, NumberOfDigits As Integer) As String
Dim Result As String
While Number > 0
Result = Number Mod NumberSystem & Result
Number = Int(Number / NumberSystem)
Wend
If NumberOfDigits = 0 Then
Convert = Result
Else
Convert = Right(String(NumberOfDigits - Len(Result), "0") & Result, NumberOfDigits)
End If
End Function
--------------------------

Both of these are really nice. Kudos to both. I have to think how these calculate to determine what steps would be required to correct Damon's, as it is more convenient and far more powerful (much larger conversion range).

Possibly Damon or others can jump in, too.

Regards,
Jay