formulas for counting in bases other than 10

youbet7469

New Member
Joined
Mar 20, 2002
Messages
12
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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.<pre/>
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</pre>

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,<font size=+2><font color="red"> I<font color="blue">van<font color="red"> F M</font color="blue">oala</font></font></font>
<font color="green">[url]http://www.gwds.co.nz/ - Under Construction[/url]
This message was edited by Ivan F Moala on 2002-04-08 21:24
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top