Analysis ToolPak

Tips, Tricks, and Answers

The following articles are available for the 'Analysis ToolPak' topic. Click the article's title (shown in bold) to see the associated article.

AutoFill with Random Numbers
AutoFilling is a great tool in Excel; it allows you to place a wide range of values into cells. If you want to use it to add random numbers to a worksheet, you'll be disappointed—Excel won't do it. You can, however, use one of the tools available in the Analysis ToolPak to generate a whole bunch of random numbers.

Calculating Business Days
Excel allows you to determine the number of work days (business days) between two dates by using the NETWORKDAYS worksheet function. This tip explains different ways you can immediately put the function to work.

Calculating Fractions of Years
Excel provides, as part of the Analysis Toolpak, a worksheet function that can use to determine what percentage of a year is represented by the difference between two dates. This tip explains how to put this function to work.

Calculating Future Workdays
Need to calculate the date that is a certain number of workdays in the future? You can do so using a couple of different worksheet functions, as described in this tip.

Converting Units
Using the Analysis ToolPak for the vast conversions the CONVERT function in an Excel worksheet has availabile.

Finding the Analysis ToolPak Add-In
The Analysis ToolPak is used to add some very handy worksheet functions to Excel. If you don't have it installed, and you can't find your original Microsoft Office discs, then you may be at a loss as to what you can do. Here's some ideas.

Leap Years and Fiscal Periods
If your fiscal period ends in February, and you need to calculate the date either a year in the past or the future, then you will run into the problem of how to deal with leap years. This tip explains some of the formulas and functions you can use to achieve the desired calculation.

Random Numbers in a Range
Excel provides several different functions that you can use to generate random numbers. One of the most useful is the RANDBETWEEN function, which allows you to generate a random number between a lower and upper boundary that you specify.

Restoring the Analysis ToolPak
Add-ins for Excel, such as the Analysis ToolPak, are stored in files on your hard drive that can be deleted. If you delete the ToolPak files by accident, you may be looking for a way to get the add-in back where it belongs.

Rounding to the Nearest $50
When preparing financial reports, it may make your data easier to understand if you round it to the nearest multiple, such as the nearest $50 increment. You can do that using the worksheet functions described in this tip.

Specifying Different Weekends with NETWORKDAYS
The NETWORKDAYS worksheet function can be used to easily determine the number of work days (Monday through Friday) within a range of dates. If your workweek consists of different days, however, the NETWORKDAYS function may not be the best place to start.

Using the MROUND Worksheet Function
If you want to round a value to some multiple of a whole number, you'll want to become familiar with the MROUND function. This tip explains how you can use this function.

Using the WEEKNUM Function
The Analysis Toolpak includes the WEEKNUM function, which returns the week of the year in which a particular date falls. Understanding how the function works is important to interpreting its results.

Using the XIRR Function
The XIRR function is used to determine the rate of return with irregular payments. This tip explains how to use the function.