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:

- Part 1: Mathematical functions
- Part 2: Date/Time functions
**Part 3: Numerical system conversion functions**- Part 4: Others

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

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

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

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

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

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

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)

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.

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?

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

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.

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.

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.

Thank you for your quick input and it working.

This information has saved me HOURS if not DAYS.

Thank you.

I do not it why the replacement formula works for the DEC2BIN, can you explain ?

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.

Hi guys,

I use this hex to decimal converter and this decimal to hex converter to convert data

They are pretty good!

Hope this help

David