Replacing the Analysis Toolpak Addin – Part 1

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:

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.

Mathematical ATP functions
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

14 thoughts on “Replacing the Analysis Toolpak Addin – Part 1

  1. 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

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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?

  7. 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?

  8. 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.

  9. 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?

  10. Thanks, the information provided above has definately shed some light on my problem, but what exactly do mean by “internationalisation”?

  11. FYI,

    Microsoft annonced that the Anlysis Tool pack will be integrated into Excel 2007. Thus, this should fix the problem if someone can wait…

  12. 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

  13. 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

Leave a Reply

Your email address will not be published. Required fields are marked *