The Analysis Toolpak Addin (ATP) is one of the most often used Excel Addins and provides several useful function. But the ATP has one major drawback: If internationalisation is an issue for you using the ATP will cause problems in your spreadsheets. Unfortunately Excel does not automatically translate the ATP functions to their local equivalents then opened in a different Excel language version but leaves the formulas unchanged. This will result in #NAME (or the local equivalent error message) errors in cells which make use of these functions.
Common recommendation is to not use ATP functions. But that is easier said then done. How to replace these useful functions if you don’t want to use VBA User Defined Functions instead? The following article therefore will provide alternative formulas for some of the ATP functions. As the ATP consists of 93 formulas (if I’ve counted them correctly) this article is divided in several ATP function areas:
- Part 1: Mathematical functions
- Part 2: Date/Time functions
- Part 3: Numerical system conversion functions
- Part 4: Others
I will not provide replacement formulas for all ATP functions (mostly due to lack of knowledge) but maybe someone else will step in and provide the remaining ones. Also I’m hoping for more efficient solutions for some of the replacement formulas I’ll present.
| ATP Function | Description | ATP Syntax | Replacement Formula | Array formula |
|---|---|---|---|---|
| BESSELJ | Returns the Bessel function | BESSEJ(x,n) | =SUM(-1^(ROW(INDIRECT(“1:50”))-1)/(2^(2*(ROW(INDIRECT(“1:50”))-1)+ABS(INT(n)))*FACT(ROW(INDIRECT(“1:50”))-1)*FACT(ABS(INT(n))+(ROW(INDIRECT(“1:50”))-1)))*x^(2*(ROW(INDIRECT(“1:50”))-1)+ABS(INT(n)))) | Yes |
| DOLLARDE | Converts a dollar price (expressed as a fraction) into a dollar price expressed as a decimal number | =DOLLARDE(fractional_dollar,fraction) | =TRUNC(fractional_dollar)+((fractional_dollar-TRUNC(fractional_dollar))*10)/fraction | No |
| DOLLARFR | Converts a dollar price (expressed as a decimal number) into a dollar price expressed as a fraction | =DOLLARFR(decimal_dollar,fraction) | =TRUNC(decimal_dollar)+((decimal_dollar-TRUNC(decimal_dollar))/10)*fraction | No |
| ERF | Returns the error function integrated between lower_limit and upper_limit | =ERF(lower_limit,upper_limit) | =GAMMADIST(upper_limit^2,0.5,1,TRUE)-GAMMADIST(lower_limit^2,0.5,1,TRUE) | No |
| ERFC | Returns the complementary ERF function integrated between x and infinity | =ERFC(x) | =ChiDist(2*x^2,1) | No |
| FACTDOUBLE | Returns the double factorial of a number | =FACTDOUBLE(number) | =PRODUCT(IF(MOD(ROW(INDIRECT(“1:”&number)),2)=MOD(number,2),(ROW(INDIRECT(“1:”&number))))) | Yes |
| GCD | Returns the greatest common divisor of 2 – 29 integers | =GCD(number1,number2,…) | =MAX(IF((MOD(number2,ROW(INDIRECT(“1:”&number1)))&MOD(number1,ROW(INDIRECT(“1:”&Anumber1))))=”00?,ROW(INDIRECT(“1:”&number1)))) | Yes |
| GESTEP | Tests whether a number is greater than a threshold value | =GESTEP(number,step) | =–(number>=step) | No |
| LCM | Returns the least common multiple of 1 – 29 integers | =LCM(number1,number2, …) | =number2*MATCH(0,MOD(number2*ROW(INDIRECT(“1:”&number1)),number1),0) | Yes |
| MROUND | Returns a number rounded to the desired multiple. Midway points are rounded away from 0 | =MROUND(number,multiple) | =ROUND(number/multiple,0)*multiple | No |
| MULTINOMIAL | Returns the ratio of the factorial of a sum of values to the product of factorials | =MULTINOMIAL(number1,number2, …) | =FACT(SUM(number1,number2,…))/PRODUCT(FACT(number1,number2,…)) | Yes |
| QUOTIENT | Returns the integer portion of a division | =QUOTIENT(numerator,denominator) | =TRUNC(numerator/denominator) | No |
| RANDBETWEEN | Returns a random number between (and inclusive of) two specified numbers | =RANDBETWEEN(bottom,top) | =INT((top – bottom + 1) * RAND() + bottom) | No |
| SERIESSUM | Returns the sum of a power series expansion | =SERIESSUM(x,n,m,coefficients) | =SUMPRODUCT(coefficients,x^(n+m*(ROW(INDIRECT(“1:”&ROWS(coefficients)))-1))) | No |
| SQRTPI | Returns the square root of a number multiplied by pi | =SQRTPI(number) | SQRT(PI()*number) | No |
So this is the first part. ATP functions for which I don’t have a replacement formula in this area are: All BESSEL functions. So feel free to add them :-)
Update 21-Dec-2004:
Asser was helpful in providing a worksheet formula for the BESSEJ function. So only 3 of the 4 Bessel functions remain
Frank
Frank,
Have you considered converting all of the ATP functions? It looks as though you’ve made good progress.
Here is a BIN2DEC type conversion.
This formula does not limit to 10 digits, nor does it use the significant bit as BIN2DEC does.
=SUMPRODUCT((MID(TEXT(A1, REPT(“0?, LEN(A1))), ROW(INDIRECT(“1:” & LEN(A1))), 1)), 2^(-ROW(INDIRECT(“1:” & LEN(A1))) + LEN(A1)))
Rob
Hi Rob
the following parts of this article will just cover the other ‘areas’ of the ATP. BIN2DEC will be part of the 3rd part :-)
My BIN2DEC replacement is quite similar:
=SUMPRODUCT(MID(“
0″
&Number,ROW(INDIRECT(“1:”&LEN(“0?&Number))),1)*2^(LEN(“
0″
&Number)-ROW(INDIRECT(“
1:”
&LEN(“0?&Number)))))
Maybe an overview of the formulas I won’t cover:
– Financial formulas (maybe later)
– The conversions in HEX numerics (as I’m not able to create the characters with an array formula) but all other numeric system conversions will be covered
– Complex number functions
– YEARFRAC (as I consider this function as buggy anyway…)
– The above stated mathematical functions
– CONVERT
Frank
Hi All,
One more version (this is an array formula)
=SUM(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)*2^(LEN(A1)-ROW(INDIRECT(“1:”&LEN(A1)))))
– Asser
Hi Frank,
You gave a challenge, so of course I had to try to solve it :)
Here’s a way to calculate BESSELJ function without ATP. This is an array formula:
=SUM(-1^(ROW(INDIRECT(“1:50?))-1)/(2^(2*(ROW(INDIRECT(“1:50?))-1)+ABS(INT(B1)))*FACT(ROW(INDIRECT(“1:50?))-1)*FACT(ABS(INT(B1))+(ROW(INDIRECT(“1:50?))-1)))*$A1^(2*(ROW(INDIRECT(“1:50?))-1)+ABS(INT(B1))))
Here A1 is the x in BESSELJ and B1 is the n.
You can make this much nicer by defining ROW(INDIRECT(“1:50?))-1 as L and assuming, that people use only positive integers as n (B1). So you’ll get:
=SUM(-1^L/(2^(2*L+B1)*FACT(L)*FACT(B1+L))*$A1^(2*L+B1))
In ROW(INDIRECT(“1:50?))-1 you can use smaller area than 1:50, if your x isn’t very big. You’ll just have to try and compare with the original BESSELJ-function.
Now others can try to solve the other BESSEL-functions… or maybe I will, if I find more time and interest.
– Asser
Hi Asser,
great. I just hoped something like this would happen. I’ll update the table in the original post to include this function as well (and I’m hoping for the other 3 functions…)
Thanks
Frank
How do you get any of these functions to work?
Pasting these funtions into excell gives me errors that I do not have the knowlege or time to fix.
Say if have a 16 bit binary number 1111111111111111 typed into cell A1, what formula in cell B1 would give me the decimal equiv?
Any assistance would be greatly appreciated?
Try the following:
The binary number (in D1) must be stored as a string, otherwise the last bit is truncated by excel
=SUMPRODUCT(2^(ROW(INDIRECT(“1:”&LEN(D1)))-1),VALUE(MID(D1,LEN(D1)-ROW(INDIRECT(“1:”&LEN(D1)))+1,1)))
I am not sure what the maximum size binary number could be handled, but there will be one due the 15 digit accuracy limit of 2^…
The ROW(Indirect()) allows for a variable length binary number. Is there a simpler way to specify a variable length array of consecutive integers?
The function provided for erfc(x) does not match the table values. Consult any reference if you disagree. There is a useful approximation to the Q-function which can be converted to erfc(x). It is
Q(x)=(1/((1-(1/PI()))*A36+SQRT(A36^2+6.28318)/PI()))*(1/SQRT(2*PI()))*EXP(-(A36^2)/2)
Using the conversion erfc(x) = 2*Q(x*sqrt(2)) gives efrc as
erfc(x)=(1/((1-(1/PI()))*A36*SQRT(2)+SQRT(2*A36^2+6.28318)/PI()))*(2/SQRT(2*PI()))*EXP(-(A36^2))
One additional comment; the complementary error function is a simplification of the Q-function which is the integral of the tail of a Gaussian for values above x.
I am a printer that is trying to print a medical form that is read by the an OCR. Each page has a unique contract number like 567301 then there is a column that has a series of 24 “bubbles” (like on an SAT test). The bubbles are colored in based on converting the contract number to a binary number. Any ideas on how I could use Excel to print this?
Can you provide replacement function for XIRR also
Thanks, the information provided above has definately shed some light on my problem, but what exactly do mean by “internationalisation”?
FYI,
Microsoft annonced that the Anlysis Tool pack will be integrated into Excel 2007. Thus, this should fix the problem if someone can wait…
I got here searching for a GCD replacement. I tried your code but can’t get it to work. It looks like there might be a couple of typos. “number2? only appears once, but I think it should be in there twice. There is also a “&Anumber1? that looks wrong. Here’s my attempted fix, but it always returns “1?:
=MAX(IF((MOD(B1,ROW(INDIRECT(“1:”&A1)))&MOD(A1,ROW(INDIRECT(“1:”&B1))))=”00?,ROW(INDIRECT(“1:”&A1))))
Any ideas? Thanks-
Sean
I figured it out. It works as-is, except for the typo “&Anumber1?. I had to learn more about array formulas. I was using another one and didn’t need to hit CTRL-SHIFT-ENTER, but I did with this one. Thanks a lot for the collection. Sean