Replacing the Analysis Toolpak Addin – Part 3

This part focusses on replacing the numerical system conversion functions of the Analysis Toolpak Addin (ATP). Reference to the other parts of this article series:

Numeric System Conversion ATP functions
ATP Function Description ATP Syntax Replacement Formula Array formula
BIN2DEC Converts binary number to decimal =BIN2DEC(Number) =SUMPRODUCT(MID(“0″&Number,ROW(INDIRECT(“1:”&LEN(“0″&Number))),1)*2^(LEN(“0″&Number)-ROW(INDIRECT(“1:”&LEN(“0″&Number))))) No
BIN2OCT Converts binary number to octal =BIN2OCT(Number,Places) Combine solutions for BIN2DEC and DEC2OCT No
DEC2BIN Converts a decimal number to binary =DEC2BIN(Number) =SUMPRODUCT(INT(MOD(Number/2^(COLUMN(1:1)-1),2))*10^(COLUMN(1:1)-1)) No
DEC2OCT Converts a decimal number to octal =DEC2OCT(Number) =SUMPRODUCT(INT(MOD(Number/8^(COLUMN(1:1)-1),8))*10^(COLUMN(1:1)-1)) No
HEX2BIN Converts a hexadecimal to a binary =HEX2BIN(Number,Places) Combine solutions for HEX2DEC and DEC2BIN No
HEX2DEC Converts a hexadecimal to a decimal =HEX2DEC(Number) =SUMPRODUCT((MATCH(MID(“0″&Number,ROW(INDIRECT(“1:”&LEN(“0″&Number))),1),{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″,”A”,”B”,”C”,”D”,”E”,”F”},0)-1)*16^(LEN(“0″&Number)-ROW(INDIRECT(“1:”&LEN(“0″&Number))))) No
HEX2OCT Converts a hexadecimal to an octal =HEX2OCT(Number,Places) Combine solutions for HEX2DEC and DEC2OCT No
OCT2BIN Converts an octal number to binary =OCT2BIN(number,places) Combine solutions for OCT2DEC and DEC2BIN No
OCT2DEC Converts an octal number to decimal =OCT2DEC(number) =SUMPRODUCT(MID(“0″&Number,ROW(INDIRECT(“1:”&LEN(“0″&Number))),1)*8^(LEN(“0″&Number)-ROW(INDIRECT(“1:”&LEN(“0″&Number))))) No

Missing functions: BIN2HEX, DEC2HEX and OCT2HEX as the creation of the characters A-F which are part of a hexadecimal number is not really feasible using worksheet functions.

Frank

17 thoughts on “Replacing the Analysis Toolpak Addin – Part 3

  1. Note that VBA has the HEX() function to convert from decimal to hex, giving us the UDF:

    Public Function DEC2HEX(ByVal iDec As Integer) As String

    On Error Resume Next
    DEC2HEX = Hex(iDec)

    End Function

    which can, of course, be used in conjunction with the other functions to give BIN2HEX and OCT2HEX.

    FWIW, HEX2DEC can also be done in a 1-line VBA function:

    Public Function HEX2DEC(ByVal sHex As String) As Integer

    On Error Resume Next
    HEX2DEC = Val(“&h” & sHex)

    End Function

    Regards

    Stephen Bullen

  2. Hi Stephen
    good point. Though using VBA you could simplify many of the above formulas anyway :-)
    This was merely meant for using standard worksheet formulas to replace the ATP formulas as a VBA solution would either require to include the code in each workbook or create a separate addin (which also has to be distributed)

    Happy Holidays
    Frank

  3. Hi Frank

    Sure, but my preference is definitely to include code in the workbook to do these simple functions, rather than rely on the ATP for anything. As I’m sure you’re aware, the ATP functions aren’t translated when opening a file in a different language version of Excel, so giving #NAME! errors.

    Happy Holidays to youy too!

    Stephen

  4. Hi Stephen
    have the same preference. the initial reason for this list was just this #NAME problem as my current client has a mixture of Office installation (we counted 5 different language versions).
    So it started for some common functions (EOMONTH, etc.) and I just compiled a list of formula translations (for fun…). But for the more complex ones I also have my own addin :-))

    Frank

  5. I am trying to do either pure octal or preferably hexadecimal arithmetic in VB through Excel.
    The existing functions seem to convert my results back to decimal i.e., Hex(a) + Hex(b) is an integer number quantity that is decimal ( for a and b as integers). Is it possible to define a variable say H that remain hexadecimal so that H = hex(a) + hex(b) is a hexadecimal quantity?
    Thanks,
    Marcel

  6. Marcel,

    Computers only know one way of storing numbers – and that’s in binary. Lots of 1s and 0s.

    Decimal is what humans are used to reading.
    Computers have to specially turn the binary stored number into human readable decimal. eg. 123.456

    Some humans also prefer to read their binary stored numbers as hexadecimal. eg. 0×45A1

    Keep in mind that your computer’s CPU doesn’t know decimal or hexadecimal. It works exclusively in binary.
    Operating Systems and Applications like Excel do a good job of hiding that binary complexity by keeping it as a “behind the scenes” activity.

    Decimal and Hexadecimal are always strings.
    The Decimal and Hexadecimal numbers which you read are made by computer programs which contruct strings character by character using logic and ascii table lookups.

    Assuming a and b are recognised as numbers
    Hex(a) returns a string

    So when you say Hex(a) + Hex(b) you are trying to do arithmetic operations on strings which is just not possible.

    How about trying
    H = hex(a + b)

  7. Thanks God !

    The BIN2DEC function in the AddIn pack is bugged. That damn thing doesn’t work in numbers with larger length (I’d tryed in a 30 characters binary number). Your function does. Thanks a lot. Save my day.

  8. I’m converting binary numbers with up to 32 bits. That’s 4,286,578,691 in decimal.

    I have no problems going from BIN2DEC, but the DEC2BIN formula stops working at about 265,000,000. I get a #NUM! error.

    Any ideas?

  9. Hi Frank,

    I’ve noticed an issue with your DEC2BIN replacement function(assuming of course that I’m using it correctly).

    What I really need is Hex to Bin conversion, so as suggested I use your HEX2DEC and DEC2BIN. When I start with a number like 138d7, it converts to decimal fine (80087), but the final step gives me 10011100011010100 ( jeez I hope I typed that right!).

    That binary figure is actually 80084 dec or 138d4 hex. I’ve lost 3! That can be easily seen since the last hex digit of “7? should be “0111? in bin, not “0100?.

    Is it me?
    Thanks,
    Brian

  10. Brian: DEC2BIN returns a number. Excel can’t handle numbers greater than 15 significant digits, so if your BIN looks like a number that has that many significant digits, it will return the wrong result. Note that 32768 and 32769 return the same result because the last 1 in 32769 would be the 16th significant digit. In your example, there are 17 significant digits, so the last two are simply replaced with zeros.

    Ideally, this function would return a string to avoid this problem. Unfortunately, Excel doesn’t let you concatenate strings inside of array formulas. I can’t think of a good alternative. Hopefully someone can think of something or at least change the formula to return an error instead of incorrect results. In the mean time, use it with caution and beware of the issue.

  11. I need some help for Excel Hex2Dec function!
    I have value “10? in Cell A1 and “01? in A2.
    When I use Hex2Dec(A1&A2) and I expect to = Hex2Dec (“1001?).
    But Excel translate to Hex2Dec(“101?).
    The question is how to get Excel to translate “1001?.
    Any help will be very gratful.

  12. Frank: =HEX2DEC(TEXT(A1,”00?)&TEXT(A2,”00?))

    Your value in A2 is a number, not a string. Excel is concatenating 10 and 1, not 10 and 01. It’s only the formatting in A2 that makes it appear as 01.

  13. Still in 2009 this information is of much value, solving a big problem I’d encountered.

    Moreover, the proposed approach has opened my mind to other applications.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.