Skipping data tables when calculating workbooks
Because a data table is an array, Excel treats it as a unit, so a worksheet recalculation means that the entire data table is always recalculated. Such a recalculation is not a big problem for a small data table that has just a few dozen formulas. However, it’s not uncommon to have data tables with hundreds or even thousands of formulas, and these larger data tables can slow down worksheet recalculation.
If you’re working with a large data table, you can reduce the time it takes for Excel to recalculate the workbook if you configure Excel to bypass data tables when it’s running the recalculation. Here are the two methods you can use:
Choose Formulas ⇒ Calculation Options ⇒ Automatic Except for Data Tables.
Choose File ⇒ Options to open the Excel Options dialog box, choose Formulas, select the Automatic Except for Data Tables option, and then click OK.
Now every time you calculate a workbook, Excel bypasses the data tables.
Analyzing Data with Goal Seek
What if you already know the formula result you need and you want to produce that result by tweaking one of the formula’s input values? For example, suppose that you know that you need to have $100,000 saved for your children’s college education. In other words, you want to start an investment now that will be worth $100,000 at some point in the future.
This is called a future value calculation, and it requires three parameters:
The term of the investment
The interest rate you earn on the investment
The amount of money you invest each year
Assume that you need that money 18 years from now and that you can make a 4 percent annual return on your investment. Here’s the question that remains: How much should you invest each year to make your goal?
Sure, you could waste large chunks of your life guessing the answer. Fortunately, you don’t have to, because you can put Excel’s Goal Seek tool to work. Goal Seek works by trying dozens of possibilities — called iterations — that enable it to get closer and closer to a solution. When Goal Seek finds a solution (or finds a solution that’s as close as it can get), it stops and shows you the result.
You must do three things to set up your worksheet for Goal Seek:
Set up one cell as the changing cell, which is the formula input cell value that Goal Seek will manipulate to reach the goal. In the college fund example, the formula cell that holds the annual deposit is the changing cell.
Set up the other input values for the formula and give them proper initial values. In the college fund example, you enter 4 percent for the interest rate and 18 years for the term.
Create a formula for Goal Seek to use to reach the goal. In the college fund example, you use the FV() function, which calculates the future value of an investment given an interest rate, term, and regular deposit.
When your worksheet is ready for action, here are the steps to follow to get Goal Seek on the job:
1 Select Data ⇒ What-If Analysis ⇒ Goal Seek.The Goal Seek dialog box appears.
2 In the Set Cell box, enter the address of the cell that contains the formula you want Goal Seek to work with.
3 In the To Value text box, enter the value that you want Goal Seek to find.
4 In the By Changing Cell box, enter the address of the cell that you want Goal Seek to modify.Figure 2-7 shows an example model for the college fund calculation as well as the completed Goal Seek dialog box.
5 Click OK.Goal Seek adjusts the changing cell value until it reaches a solution. When it’s done, the formula shows the value you entered in Step 3, as shown in Figure 2-8.
6 Click OK to accept the solution.
FIGURE 2-7: Using Goal Seek to calculate the annual deposit required to end up with $100,000 in a college fund.
FIGURE 2-8: Goal Seek took all of a second or two to find a solution.
You can get a more accurate solution by increasing the number of iterations that Goal Seek can use, by reducing the value that Goal Seek uses to mark a solution as close enough, or both. Choose File ⇒ Options and then choose Formulas. Increase the value of the Maximum Iterations spin button, decrease the value in the Maximum Change text box, or both, and then click OK.
Analyzing Data with Scenarios
Many formulas require a number of input values to produce a result. Previously in this chapter, in the “Working with Data Tables” section, I talk about using data tables to quickly see the results of varying one or two of those input values. Handy stuff, for sure, but when you’re analyzing a formula’s results, manipulating three or more input values at a time and performing this manipulation in some systematic way often help. For example, one set of values might represent a best-case approach, whereas another might represent a worst-case approach.
In Excel, each of these coherent sets of input values — known as changing cells — is called a scenario. By creating multiple scenarios, you can quickly apply these different value sets to analyze how the result of a formula changes under different conditions.
Excel scenarios are a powerful data-analysis tool for a number of reasons. First, Excel enables you to enter up to 32 changing cells in a single scenario, so you can create models that are as elaborate as you need. Second, no matter how many changing cells you have in a scenario, Excel enables you to show the scenario’s result with just a few taps or clicks. Third, because the number of scenarios you can define is limited only by the available memory on your computer, you can effectively use as many scenarios as you need to analyze your data model.
Group all your changing cells in one place and label them.
Make