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:
- 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
Stephen Bullen:
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
21 December 2004, 3:47 amFrank Kabel:
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
21 December 2004, 4:17 amFrank
Stephen Bullen:
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
21 December 2004, 10:11 amFrank Kabel:
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
21 December 2004, 10:56 amMarcel Meicler:
I am trying to do either pure octal or preferably hexadecimal arithmetic in VB through Excel.
5 May 2005, 10:14 amThe 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
Rob van Gelder:
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
6 May 2005, 1:49 amH = hex(a + b)
Alexander Blagus:
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.
19 October 2005, 7:10 amMarc:
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?
11 December 2006, 7:21 pmBrian:
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?
6 February 2007, 2:24 pmThanks,
Brian
Dick Kusleika:
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.
6 February 2007, 6:30 pmFrank_K:
I need some help for Excel Hex2Dec function!
3 July 2007, 5:20 pmI 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.
Dick Kusleika:
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.
4 July 2007, 9:20 amFrank_K:
Thank you for your quick input and it working.
5 July 2007, 9:45 amscott:
This information has saved me HOURS if not DAYS.
20 May 2008, 1:30 pmThank you.