Funcres.xlam and Analysis Toolpak in Excel/VBA

An introduction to funcres.xlam

Funcres.xlam is an add-in available within Microsoft Excel. This is an “Analysis Toolpak" and includes 19 different features and various statistical functions to help you work with the data available in your Excel worksheets.

In order to use this add-in, which is installed by default, we must simply enable and activate it.

Steps to activate funcres.xlam

  1. In the Microsoft Excel menu, select File –> Options.
  2. Click Add-ins in the navigation pane on the left-hand side.
  3. Click the Go button, next to the “Manage: Excel Add-ins" dropdown button (near the bottom of the dialog) as shown below.

4. After clicking on the “Go" button, a new Add-ins window will be opened as shown below.

5. In the “Add-ins" window, select the check box of Analysis ToolPak (as shown in the above snapshot) to enable it.

Note: Analysis ToolPak – VBA is a similar add-in, which can be used when you’re planning to use macros in Microsoft Excel.

6. Then click on the “Ok" button.

7. Once the steps above are followed, close and reopen Microsoft Excel for the changes to take effect.

8. You’ll see that Funcres.xlam is associated with the Microsoft Excel program once you reopen MS Excel.

Users gain access to the built-in features of the add-in once it is enabled. Because it is password protected by Microsoft, it provides only the functionality to the users and not its code.

The code is in a compiled format (Dynamic Linked Libraries). These DLLs can be included and used in our programs as required. You’re not able to make changes to the funcres.xlam code because as it could lead to issues with the working of the existing functions.

Here are some examples of special functionality supported by funcres.xlam:

S.no Functionality Description
1 Anova – Single Factor An analysis of variance done to find if there is any relationsip between any two sets of data.
2 Anova – 2 Factors with Replication An analysis of variance done to find if there is any relationsip between any two sets of data. More than one sample of data is considered for each group.
3 Anova – 2 Factor without Replication An analysis of variance done to find if there is any relationsip between any two sets of data. Only one sample of data is considered for each group.
4 Correlation Two sets of data are compared to determine if there is any relationship between them.
5 Covariance The means of each data set is calculated from the average of the product of deviations of values.
6 Descriptive Statistics Calculates several statistical measurements and lists them as a table. This table provides calculations for the items like Mean, Standard Error, Median, Mode, Standard Deviation, Sample Variance, Kurtosis, Skewness, Range, Minimum, Maximum, Sum, Count, Largest, Smallest and Confidence Level.
7 Exponential Smoothing Smoothens data while weighting the recent data heavily.
8 F-Test Two Sample for Variances The variances between two groups of data are compared.
9 Fourier Analysis This is nothing but Discrete Fourier Transform (DFT) / Fast Fourier Transform (FFT) method which includes reverse transforms.
10 Histogram Occurrences in each of many data bins is counted.
11 Moving Average A moving average is calculated to allow us to smooth out a series of data that has peaks.
12 Random Number Generation Different types of random numbers are created. Example: Uniform, Normal, Bernoulli, Poisson, Patterned and Discrete.
13 Rank and Percentile Several number ranking from the highest to the lowest are created. Percentile values that indicate how one individual number’s rank is compared with the other numbers is also provided.
14 Sampling A population is sampled periodically or randomly.

A disadvantage of using Funcres.xlam: performance

While Funcres.xlam is an efficient add-in supporting several rare functions, it has one major disadvantage. It consumes additional CPU resources depending on the functionality it is used for. This eventually reduces the performance of MS Excel, and even your entire system at times. Below are some issues you might face as a result of poor performance of the CPU:

  • MS. Excel takes a lot of time to load (longer than the expected time)
  • Other programs in MS Excel might not function properly anymore
  • The entire system might stop working / shut down
  • You may find it difficult to open any program in your system as it might lead to a “Not Responding" situation

To overcome these downsides, we must either wait for Microsoft Excel to respond or kill the existing instance and re-open it again to make Microsoft Excel work as expected.

Note: This still does not guarantee that the issue is resolved.

How to remove Funcres.xlam from Excel

If we feel that Funcres.xlam is consuming too much CPU, causing other issues with existing Microsoft Excel programs, or it’s simply not needed anymore, it can be easily removed by reversing the same steps stated earlier in this article.

In the Add-ins window, we can un-check the check box of Analysis ToolPak (as shown in the above snapshot) to disable it and click on the “OK" button.

As usual, the host application MS Excel needs to be closed and opened again for this deactivation to take effect.

Summary

Funcres.xlam is an Analysis Toolpak provided by Microsoft Excel as an add-in to utilize various functions related to mathematical analysis that are useful for performing statistical calculations at the organizational level.

Important points that need to be considered while using Funcres.xlam:

  • It may consume too many resources in your CPU, which in turn may impact your system’s performance or our existing program functionality. Hence, this add-in should not be enabled unless you really need the additional analysis toolkit.
  • Analysis Toolpak – VBA (a part of Funcres.xlam) should be enabled only if  the worksheet function uses VBA code in Microsoft Excel.

Leave a Reply

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