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

 

[Damon Ostrander]

Hi youbet,

There should have been just one backslash in my formula

Digit = X Base ^ i

For some reason, every time a message is posted all the backslashes get doubled. Anyone know why?

Regarding correct counting, in base 5 the correct counting is

1,2,3,4,10,11,12,13,14,20,21,22,23,24,30...41,42,43,44,100...

Note that digit "5" never occurs in base 5, just like there is no digit "10" in base 10 (10 is two digits, a 1 and a 0). The value represented by 10 in base 5 is 1 x 5^1 + 0 x 5^0, or five. 10 in any base is the base itself.

Incidentally, I just checked my code for base 5 and it does provide exactly the sequence shown above. I also checked it against Excel's DEC2BIN, DEC2OCT, and DEC2HEX, and it duplicates their results.

Happy counting.

Damon

 

[Jay Petrulis]

On 2002-04-10 10:13, Damon Ostrander wrote:
Hi youbet,

There should have been just one backslash in my formula

Digit = X Base ^ i

For some reason, every time a message is posted all the backslashes get doubled. Anyone know why?

Regarding correct counting, in base 5 the correct counting is

1,2,3,4,10,11,12,13,14,20,21,22,23,24,30...41,42,43,44,100...

Note that digit "5" never occurs in base 5, just like there is no digit "10" in base 10 (10 is two digits, a 1 and a 0). The value represented by 10 in base 5 is 1 x 5^1 + 0 x 5^0, or five. 10 in any base is the base itself.

Incidentally, I just checked my code for base 5 and it does provide exactly the sequence shown above. I also checked it against Excel's DEC2BIN, DEC2OCT, and DEC2HEX, and it duplicates their results.

Happy counting.

Damon

Hi Damon,

Excellent job with the formula.

Base 3 and base 9 seem to have a problem only at the exponential points (bad term -- I'm thinking of the 10's, 100's, 1000's place equivalents).

I don't see how it is going wrong, though, and only with base 3 and 9. The intermediate value for digit appears correct by my tests, yet "3" and "9" appear in the result.

Any thoughts?

Regards,
Jay
This message was edited by Jay Petrulis on 2002-04-10 22:29

 

[Damon Ostrander]

Thanks for checking out my function Jay, and for find the problem for 3 and 9. Probably the problem would also occur for other numbers as well. It stems from the slight inaccuracy in the Log calculation and division, and can be corrected by changing the line of code that computes K to:

K = Int(Log(X + 0.5) / Log(Base))

Thanks again. Keep Excelling.

Damon

 

[Jay Petrulis]

On 2002-04-11 16:04, Damon Ostrander wrote:
Thanks for checking out my function Jay, and for find the problem for 3 and 9. Probably the problem would also occur for other numbers as well. It stems from the slight inaccuracy in the Log calculation and division, and can be corrected by changing the line of code that computes K to:

K = Int(Log(X + 0.5) / Log(Base))

Thanks again. Keep Excelling.

Damon

Hi Damon,

Perfect! Only 3,9, and 27 were off, and only on the integer exponents of the values (i.e 3^n, 9^n, and 27^n for integer n)

Awesome job on this.

Bye,
Jay

 

[Jakcavar]

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

I am having a syntax error with the line :
Digit = X Base ^ i
lease help

 

[pgc01]

Hi
Welcome to the board

Try:

Digit = X \ Base ^ i

 

[Rick Rothstein]

I am having a syntax error with the line :
Digit = X Base ^ i
lease help

You may find my conversion routines of some interest (they can handle very large numbers if need be)...

Convert a Number in Any Base (Up To 36) to a (Very Large) Positive Decimal Number

Convert a (Possibly) Very Large Positive Decimal Number to Any Base (Up To 36)

 

[Jakcavar]

Hi
Welcome to the board

Try:

Digit = X \ Base ^ i

thanks

 

[pgc01]

You're welcome. Thanks for the feedback.

 

[rwickberg]

wow, this is an old thread, but if you want o know how to count in other bases than 10 without
VBA, number column A 1 through however high you want to count. Call the base b. Then, figure out how many digits you need in your new base, call that n. The fomula to put in B1 is

=mod(int(A1,b^(n-1),b)&mod(int(A1,b^(n-2),b)&mod(int(A1,b^(n-3),b)...... mod(int(A1,b^(1),b)&mod(int(A1,b^(0),b)

This will work for any number base less than 10. Beyond that, you need to start using extra symbols, usually letters, for the digits that represent 10,11,..... So you'll have to use something like a switch function to do that substitution. The math still works, you just don't want to literally concatenate "10" if the math evaluates to 10, you want "a", etc.