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
When entering data into a worksheet, you may have a need to fill a range of cells with a group of random numbers. This task is easy to do using the approaches outlined in this tip.

   Calculating Business Days
There are calendar days and then there are business days. Excel provides the NETWORKDAYS function that is helpful to figure out how many business days there are between two dates.

   Calculating Fractions of Years
When working with dates and the relationship between dates, Excel provides a variety of worksheet functions that may prove helpful. One such function is YEARFRAC, which allows you to calculate what fraction of a year is represented by the number of days between two dates.

   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 are some ideas.

   Finding the Previous Work Day
Excel has a number of functions that are available as an add-on in the Analysis ToolPak. One of these functions allows you to return the date of a previous workday. This tip discusses using this tool.

   Leap Years and Fiscal Periods
Need to figure out when a fiscal year ends when that period does not correspond to the calendar year? Here are some ways that you can do the calculation and make sure you take leap years into account.

   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
Need to know which week of the year a particular date falls within? Excel provides the WEEKNUM function so you can easily calculate this statistic.

   Using the XIRR Function
One of the financial worksheet functions provided in Excel is the XIRR function. This is used to figure out an internal rate of return when the payments associated with an investment are not evenly spaced. Here's how to use the function.