MS Excel and the Analysis Toolpak functions

Tools for acoustic calculations -

you must be logged in to download these tools

MS Excel and the Analysis Toolpak functions

Postby Eric.Desart » Sun Jul 17, 2005 10:18 am

MS Excel files and Analysis Toolpak functions
MS Excel contains 232 standard worksheet functions accessible via the Function Wizard and Help topics (Counted in MS office 97. Later editions contain some additional functions).
The Analysis Toolpak Add-In adds an additional 93 worksheet functions to the standard available 232 MS Excel 97 functions (+ another 38, mostly Statistical, command functions), also accessible via the standard Function Wizard and related Help topics.
For the less experienced user, it's not always that evident to distinguish them from one another.  They all work in a comparable manner and are shown via the Function Wizard as all other standard functions.
This means that ca 30% of the total of 329 possible MS Excel 97 worksheet functions are Analysis ToolPak Add-In Worksheet functions, equaling ca 42% of the standard 232 MS Excel 97 worksheet functions.

Analysis Toolpak functions and Studiotips
One will notice that several MS Excel files contributed or made by Studiotips members make use of one or more MS Excel functions which are integrated in this MS Excel Add-In called Analysis Toolpak (mostly complex math with imaginary values).
With a normal FULL MS Office Installation this Add-In is standard Installed but NOT activated.
If this Add-In is NOT activated while such functions are used by the Author of the Excel File such a spreadsheet will show #NAME error values (sometimes others).
Excel will not recognize this function and will show this error, unable to calculate the related formula and all its dependents.

  • Activating the Analysis Toolpak Add-In (English MS Excel edition)
    Check via the Menu Tools > Add-Ins > Analysis Toolpak. That checkbox should be checked.
    If not, just check it, press OK and the Add-In will be loaded (and standard be loaded every time you start MS Excel until you uncheck it again)
  • Installing the Analysis Toolpak Add-In (in most cases it will be installed already)
    If the name Analysis Toolpak isn't there (above check) it means it isn't installed.
    The Analysis Toolpak is standard on your MS Office CD, and standard installed if you choose complete installation.
    If it shouldn't just adjust your installation via your standard related Operating System procedures. You'll notice that MS Office allows to install individual components.
Or search the MS Excel help for Analysis Toolpak

If you feel uncertain and want to know if the Analysis Toolpak Add-In works as it should for you .......
Try this typical Analysis Toolpak formula (English Excel edition):
the result should be 1.
if an error shows then your Analysis Toolpak isn't installed, or installed but not activated.  This then means that those 93 MS Excel worksheet functions are not available to you.

Analysis Toolpak functions and different language editions of MS Excel.
This has been a limitation within MS Excel from the very first until and including the current MS Excel editions, even when the much more expensive Multi-Language edition of MS Office is installed.

While standard MS Excel functions are automatically translated and functional in all language versions of MS Excel this is NOT the case for these Analysis Toolpak functions.
This means that e.g. an English made Excel file using such functions opened in a foreign language version of Excel will ALWAYS show this #NAME error (error name in local language of course) independent if the Analysis Toolpak is correctly activated or not.

Practically this means that some files as contributed or linked by the Studiotips members Chris Whealy and Interface, just won't work in other than English MS Excel editions.

When such functions are exclusively used on NON-protected pages, installing the ATPT (Analysis Toolpak Translator) utility designed by Eric Desart can solve this problem, making such files usable in 11 main language editions of MS Excel.
However this utility is designed (for now) that it will NOT adjust Analysis Toolpak functions integrated in protected pages.

This can maybe be a tip for File Authors: If you want accessible pages to be protected put such Analysis Toolpak functions on an unprotected, deep hidden (programmatically) or standard hidden sheet and just enter a reference to the related cell.
Even when integrated in deep hidden sheets the utility will adjust the file for use in the local language edition of MS Excel.
This tool can be found and downloaded via:
Eric's Calculators & Files
Posts: 2461
Joined: Tue Feb 10, 2004 4:29 am
Location: Antwerp, Belgium

Return to Calculation tools

Who is online

Users browsing this forum: No registered users and 1 guest