formulas for counting in bases other than 10
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: formulas for counting in bases other than 10

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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


  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Woops....sorry that CONVERTS from Base10
    to your base......sorry....will have another
    play...
    Kind Regards,
    Ivan F Moala From the City of Sails

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Evening all,

    would this be the same as the =MOD function ?

    (I dont know, hence the question)


  6. #6
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  7. #7
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  10. #10
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com