Introduction to what-if analysis

By using what-if analysis tools in Microsoft Office Excel, you can use several different sets of values in one or more formulas to explore all the various results.

For example, you can do what-if analysis to build two budgets that each assumes a certain level of revenue. Or, you can specify a result that you want a formula to produce, and then determine what sets of values will produce that result. Excel provides several different tools to help you perform the type of analysis that fits your needs.

In this article

Overview

Use scenarios to consider several many different variables

Use Goal Seek to find out how to get a desired result

Use data tables to see the effects of one or two variables on a formula

Prepare forecasts and advanced business models

Overview

What-if analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet.

Three kinds of what-if analysis tools come with Excel: scenarios, data tables, and Goal Seek. Scenarios and data tables take sets of input values and determine possible results. A data table works only with one or two variables, but it can accept many different values for those variables. A scenario can have multiple variables, but it can accommodate only up to 32 values. Goal Seek works differently from scenarios and data tables in that it takes a result and determines possible input values that produce that result.

In addition to these three tools, you can install add-ins that help you perform what-if analysis, such as the Solver add-in. The Solver add-in is similar to Goal Seek, but it can accommodate more variables. You can also create forecasts by using the fill handle and various commands that are built into Excel. For more advanced models, you can use the Analysis Pack add-in.

 Top of Page

Use scenarios to consider many different variables

A scenario is a set of values that Excel saves and can substitute automatically in cells on a worksheet. You can create and save different groups of values on a worksheet and then switch to any of these new scenarios to view different results.

For example, suppose you have two budget scenarios: a worst case and a best case. You can use the Scenario Manager to create both scenarios on the same worksheet, and then switch between them. For each scenario, you specify the cells that change and the values to use for that scenario. When you switch between scenarios, the result cell changes to reflect the different changing cell values.

Worst case scenario

Changing cells

Result cell

Best case scenario

Changing cells

Result cell

If several people have specific information in separate workbooks that you want to use in scenarios, you can collect those workbooks and merge their scenarios.

After you have created or gathered all the scenarios that you need, you can create a scenario summary report that incorporates information from those scenarios. A scenario report displays all the scenario information in one table on a new worksheet.

Scenario summary report

 Note    Scenario reports are not automatically recalculated. If you change the values of a scenario, those changes will not show up in an existing summary report. Instead, you must create a new summary report.

 Top of Page

Use Goal Seek to find out how to get a desired result

If you know the result that you want from a formula, but you are not sure what input value the formula requires to get that result, you can use the Goal Seek feature. For example, suppose that you need to borrow some money. You know how much money you want, how long a period you want in which to pay off the loan, and how much you can afford to pay each month. You can use Goal Seek to determine what interest rate you must secure in order to meet your loan goal.

 Note    Goal Seek works with only one variable input value. If you want to determine more than one input value, for example, the loan amount and the monthly payment amount for a loan, you should instead use the Solver add-in. For more information about the Solver add-in, see the section Prepare forecasts and advanced business models, and follow the links in the See Also section.

 Top of Page

Use data tables to see the effects of one or two variables on a formula

If you have a formula that uses one or two variables, or multiple formulas that all use one common variable, you can use a data table to see all the outcomes in one place. Using data tables makes it easy to examine a range of possibilities at a glance. Because you focus on only one or two variables, results are easy to read and share in tabular form. If automatic recalculation is enabled for the workbook, the data in data tables immediately recalculates; as a result, you always have fresh data.

A one-variable data table

A data table cannot accommodate more than two variables. If you want to analyze more than two variables, you can use scenarios. Although it is limited to only one or two variables, a data table can use as many different variable values as you want. A scenario can have a maximum of 32 different values, but you can create as many scenarios as you want.

 Top of Page

Prepare forecasts and advanced business models

If you want to prepare forecasts, you can use Excel to automatically generate future values that are based on existing data, or to automatically generate extrapolated values that are based on linear trend or growth trend calculations.

You can fill in a series of values that fit a simple linear trend or an exponential growth trend by using the fill handle or the Series command. To extend complex and nonlinear data, you can use worksheet functions or the regression analysis tool in the Analysis ToolPak Add-in. For more information, follow the links in the See Also section.

Although Goal Seek can accommodate only one variable, you can project backward for more variables by using the Solver add-in. By using Solver, you can find an optimal value for a formula in one cell — called the target cell — on a worksheet.

Solver works with a group of cells that are related to the formula in the target cell. Solver adjusts the values in the changing cells that you specify — called the adjustable cells — to produce the result that you specify from the target cell formula. You can apply constraints to restrict the values that Solver can use in the model, and the constraints can refer to other cells that affect the target cell formula.

 Top of Page

See Also

• Calculate multiple results by using a data table
• Define and solve a problem by using Solver
• Project values in a series
• Switch between various sets of values by using scenarios
• Use Goal Seek to find the result you want by adjusting an input value

Excel > What-if analysis

Calculate multiple results by using a data table

Excel 2007

A data table is a range of cells that shows how changing one or two variables in your formulas (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) will affect the results of those formulas. Data tables provide a shortcut for calculating multiple results in one operation and a way to view and compare the results of all the different variations together on your worksheet.

In this article

Overview

Create a one-variable data table

Add a formula to a one-variable data table

Create a two-variable data table

Speed up calculation on a worksheet that contains data tables

Overview

Data tables are part of a suite of commands that are called what-if analysis tools. When you use data tables, you are doing what-if analysis.

What-if analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. For example, you can use a data table to vary the interest rate and term length that are used in a loan to determine possible monthly payment amounts.

Kinds of what-if analysis  There are three kinds of what-if analysis tools in Excel: scenarios, data tables, and goal seek. Scenarios and data tables take sets of input values and determine possible results. Goal Seek works differently from scenarios and data tables in that it takes a result and determines possible input values that produce that result.

Like scenarios, data tables help you explore a set of possible outcomes. Unlike scenarios, data tables show you all the outcomes in one table on one worksheet. Using data tables makes it easy to examine a range of possibilities at a glance. Because you focus on only one or two variables, results are easy to read and share in tabular form.

A data table cannot accommodate more than two variables. If you want to analyze more than two variables, you should instead use scenarios. Although it is limited to only one or two variables (one for the row input cell and one for the column input cell), a data table can include as many different variable values as you want. A scenario can have a maximum of 32 different values, but you can create as many scenarios as you want.

For information about what-if analysis tools, follow the links in the See Also section.

Data table basics

You can create one-variable or two-variable data tables, depending on the number of variables and formulas that you want to test.

One-variable data tables  Use a one-variable data table if you want to see how different values of one variable in one or more formulas will change the results of those formulas. For example, you can use a one-variable data table to see how different interest rates affect a monthly mortgage payment by using the PMT function. You enter the variable values in one column or row, and the outcomes are displayed in an adjacent column or row.

In the following illustration, cell D2 contains the payment formula, =PMT(B3/12,B4,-B5), which refers to the input cell B3.

A one-variable data table

Two-variable data tables  Use a two-variable data table to see how different values of two variables in one formula will change the results of that formula. For example, you can use a two-variable data table to see how different combinations of interest rates and loan terms will affect a monthly mortgage payment.

In the following illustration, cell C2 contains the payment formula, =PMT(B3/12,B4,-B5), which uses two input cells, B3 and B4.

A two-variable data table

Data table calculations  Data tables are recalculated whenever a worksheet is recalculated, even if they have not changed. To speed up calculation of a worksheet that contains a data table, you can change the Calculation options to automatically recalculate the worksheet but not the data tables. See the section Speed up calculation in a worksheet that contains data tables.

 Top of Page

Create a one-variable data table

A one-variable data table has input values that are listed either down a column (column-oriented) or across a row (row-oriented). Formulas that are used in a one-variable data table must refer to only one input cell (input cell: The cell in which each input value from a data table is substituted. Any cell on a worksheet can be the input cell. Although the input cell does not need to be part of the data table, the formulas in data tables must refer to the input cell.).

1. Type the list of values that you want to substitute in the input cell either down one column or across one row. Leave a few empty rows and columns on either side of the values.
2. Do one of the following:
   • If the data table is column-oriented (your variable values are in a column), type the formula in the cell one row above and one cell to the right of the column of values. The one-variable data table illustration shown in the Overview section is column-oriented, and the formula is contained in cell D2.

If you want to examine the effects of various values on other formulas, type the additional formulas in cells to the right of the first formula.

• If the data table is row-oriented (your variable values are in a row), type the formula in the cell one column to the left of the first value and one cell below the row of values.

If you want to examine the effects of various values on other formulas, type the additional formulas in cells below the first formula.

3. Select the range of cells that contains the formulas and values that you want to substitute. Based on the first illustration in the preceding Overview section, this range is C2:D5.
4. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Data Table.
5. Do one of the following:
   • If the data table is column-oriented, type the cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.) for the input cell in the Column input cell box. Using the example shown in the first illustration, the input cell is B3.
   • If the data table is row-oriented, type the cell reference for the input cell in the Row input cell box.

 Note    After you create your data table, you might want to change the format of the result cells. In the illustration, the result cells are formatted as currency.

 Top of Page

Add a formula to a one-variable data table

Formulas that are used in a one-variable data table must refer to the same input cell.

1. Do one of the following:
   • If the data table is column-oriented (your variable values are in a column), type the new formula in a blank cell to the right of an existing formula in the top row of the data table.
   • If the data table is row-oriented (your variable values are in a row), type the new formula in a blank cell below an existing formula in the first column of the data table.
2. Select the range of cells that contains the data table and the new formula.
3. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Data Table.
4. Do one of the following:
   • If the data table is column-oriented, type the cell reference for the input cell in the Column input cell box.
   • If the data table is row-oriented, type the cell reference for the input cell in the Row input cell box.

 Top of Page

Create a two-variable data table

A two-variable data table uses a formula that contains two lists of input values. The formula must refer to two different input cells.

1. In a cell on the worksheet, enter the formula that refers to the two input cells.

In the following example, in which the formula's starting values are entered in cells B3, B4, and B5, you type the formula =PMT(B3/12,B4,-B5) in cell C2.

2. Type one list of input values in the same column, below the formula.

In this case, type the different interest rates in cells C3, C4, and C5.

3. Enter the second list in the same row as the formula, to its right.

Type the loan terms (in months) in cells D2 and E2.

4. Select the range of cells that contains the formula (C2), both the row and column of values (C3:C5 and D2:E2), and the cells in which you want the calculated values (D3:E5).

In this case, select the range C2:E5.

5. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Data Table.
6. In the Row input cell box, enter the reference to the input cell for the input values in the row.

Type cell B4 in the Row input cell box.

7. In the Column input cell box, enter the reference to the input cell for the input values in the column.

Type B3 in the Column input cell box.

8. Click OK.

Example  A two-variable data table can show how different combinations of interest rates and loan terms will affect a monthly mortgage payment. In the following illustration, cell C2 contains the payment formula, =PMT(B3/12,B4,-B5), which uses two input cells, B3 and B4.

 Top of Page

Speed up calculation on a worksheet that contains data tables

1. Click the Microsoft Office Button , click Excel Options, and then click the Formulas category.
2. In the Calculation options section, under Calculate, click Automatic except for data tables.

Tip  Alternatively, on the Formulas tab, in the Calculation group, click the arrow on Calculation Options, and then click Automatic Except Data Tables.

 Note    When you select this calculation option, data tables are skipped when the rest of the workbook is recalculated. To manually recalculate your data table, select its formulas and then press F9.

 Top of Page

Excel > What-if analysis

Switch between various sets of values by using scenarios

Excel 2007

A scenario is a set of values that Microsoft Office Excel saves and can substitute automatically on your worksheet. You can create and save different groups of values as scenarios on a worksheet and then switch between these scenarios to view the different results.

If several people have specific information that you want to use in scenarios, you can collect the information in separate workbooks, and then merge the scenarios from the different workbooks into one.

After you have all the scenarios you need, you can create a scenario summary report that incorporates information from all the scenarios.

In this article

Overview

Create a scenario

Display a scenario

Merge scenarios

Create a scenario summary report

Overview

Scenarios are part of a suite of commands called what-if analysis tools. When you use scenarios, you are doing what-if analysis.

What-if analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. You can use scenarios to create and save different sets of values and switch between them. You can also create a scenario summary report, which combines all the scenarios on one worksheet. For example, you can create several different budget scenarios that compare various possible income levels and expenses, and then create a report that lets you compare the scenarios side-by-side.

Kinds of what-if analysis  There are three kinds of what-if analysis tools in Excel: scenarios, data tables, and goal seek. Scenarios and data tables take sets of input values and project forward to determine possible results. Goal seek differs from scenarios and data tables in that it takes a result and projects backwards to determine possible input values that produce that result.

Like data tables, scenarios help you explore a set of possible outcomes. Unlike data tables, scenarios from several different worksheets or workbooks can be merged. Scenarios make it easy to gather data about possible outcomes from a variety of sources, and then combine the data.

Each scenario can accommodate up to 32 variable values. If you want to analyze more than 32 values, and the values represent only one or two variables, you can use data tables. Although it is limited to only one or two variables (one for the row input cell and one for the column input cell), a data table can include as many different variable values as you want. A scenario can have a maximum of 32 different values, but you can create as many scenarios as you want.

For information about other what-if analysis tools, see the See Also section.

Scenario basics

Creating scenarios   Suppose that you want to create a budget but are uncertain of your revenue. By using scenarios, you can define different possible values for the revenue and then switch between scenarios to perform what-if analyses.

 Note    This section explains how to use scenarios, and shows sample data and the results of applying scenarios that use that data. It does not provide step-by-step instructions. For step-by-step instructions, see the section Create a scenario, later in this article.

For example, assume that your worst case budget scenario is Gross Revenue of $50,000 and Costs of Goods Sold of $13,200, leaving $36,800 in Gross Profit. To define this set of values as a scenario, you first enter the values in a worksheet, as shown in the following illustration:

Changing cells have values that you type in.

The result cell contains a formula that is based on the changing cells (in this illustration, =B1-B2).

You then use the Scenario Manager dialog box to save these values as a scenario, name the scenario Worst Case, and specify that cells B1 and B2 are values that change between scenarios.

 Note    Although this example contains only two changing cells (B1 and B2), a scenario can contain up to 32 cells.

Now suppose that your best case budget scenario is Gross Revenue of $150,000 and Costs of Goods Sold of $26,000, leaving $124,000 in Gross Profit. To define this set of values as a scenario, you create another scenario, name it Best Case, and supply different values for cell B1 (150,000) and cell B2 (26,000). Because Gross Profit (cell B3) is a formula— the difference between Revenue (B1) and Costs (B2)—  you do not change cell B3 for the Best Case scenario.

After you save a scenario, it becomes available on the list of scenarios that you can use in your what-if analyses. Given the values in the preceding illustration, if you chose to display the Best Case scenario, the values in the worksheet would change to resemble the following illustration:

Changing cells

Result cell

Merging scenarios  There may be times when you have all the information in one worksheet or workbook that is required to create all the scenarios that you want to consider. However, you may want to gather scenario information from other sources. For example, suppose you are trying to create a budget for a larger company. You might collect scenarios from different departments, such as Payroll, Production, Marketing, and Legal, because each of these sources has different information to use in creating scenarios.

You can gather these scenarios into one worksheet by using the Merge command. Each source can supply as many or as few changing cell values as you want. For example, you might want each department to supply expenditure projections, but only need revenue projections from a few.

When you collect different scenarios from various sources, you should use the same cell structure in each of the workbooks. For example, Revenue might always go in cell B2 and Expenditures might always go in cell B3. If you use different structures for the scenarios from various sources, it can be difficult to merge the results.

Tip  Consider first creating a scenario yourself, and then sending your colleagues a copy of the workbook that contains that scenario. This makes it easier to be sure that all the scenarios are structured the same way.

Scenario summary reports   To compare several scenarios, you can create a report that summarizes them on the same page. The report can list the scenarios side by side or present them in a PivotTable report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.). A scenario summary report based on the preceding two example scenarios would look something like the following:

 Note    By default, the summary report uses cell references to identify the changing cells and result cells. For this example, names were created for those cells to make the summary report easier to read. If you create names for the cells before you run the summary report, the report will contain the names instead of cell references.

A note appears at the end of the summary report explaining that the Current Values column represents the values of changing cells at the time the Scenario Summary Report was created, and that the cells that changed for each scenario are highlighted in gray.

For more information about creating a scenario summary report, see the section Create a scenario summary report.

 Top of Page

Create a scenario

Before you create a scenario, you should have an initial set of values already on the worksheet. To make scenario summary reports easier to read, you should also consider naming the cells that you plan to use in scenarios. For Help on naming cells, see the topic Use names to clarify formulas.

1. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Scenario Manager.
2. Click Add.
3. In the Scenario name box, type a name for the scenario.
4. In the Changing cells box, enter the references for the cells that you want to specify in your scenario. For example, if you want to see how changing the values of cells B1 and B2 will affect the outcome of a formula based on those cells, enter B1,B2.

 Note    To preserve the initial values for the changing cells, add a scenario that uses those values before you create additional scenarios that use different values.

5. Under Protection, select the options that you want.

 Note    These options apply only to protected worksheets. For more information about protected worksheets, see the See Also section.

• Select Prevent Changes to prevent editing of the scenario when the worksheet is protected.
• Select Hidden to prevent display of the scenario when the worksheet is protected.
6. Click OK.
7. In the Scenario Values dialog box, type the values that you want to use in the changing cells for this scenario.
8. To create the scenario, click OK.
9. If you want to create additional scenarios, repeat steps 2 through 8. After you finish creating scenarios, click OK, and then click Close in the Scenario Manager dialog box.

 Top of Page

Display a scenario

When you display a scenario, you switch to the set of values that are saved as part of that scenario. The scenario values are displayed in the cells that change from scenario to scenario, in addition to the results cells. For example, using the preceding scenarios, if you display the Best Case scenario, cell B1 displays 150000, cell B2 displays 26000, and cell B3 displays 124000.

1. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Scenario Manager.
2. Click the name of the scenario that you want to display.
3. Click Show.

 Note    After you close the Scenario Manager dialog box, the values from the last scenario that you displayed remain on the worksheet. If you saved your initial values as a scenario, you can display those values before you close the Scenario Manager dialog box.

 Top of Page

Merge scenarios

1. Select the worksheet in which to store the merged scenarios results.
2. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Scenario Manager.
3. Click Merge.
4. In the Merge Scenarios dialog box, click the arrow next to Book and select a workbook that contains scenarios that you want to merge in your results.
5. In the Sheet box, click the name of the worksheet that contains scenarios that you want to merge.
6. Click OK to merge the scenarios from the selected worksheet into the current worksheet.

The Merge Scenarios dialog box closes, and the scenarios that you merged now appear in the Scenario Manager dialog box.

7. Repeat the preceding four steps as needed until you have merged all the scenarios that you want.

When you are finished, the scenarios that you merged are all part of the current worksheet. You can close the Scenario Manager dialog box, or leave it open to continue your analysis.

 Top of Page

Create a scenario summary report

1. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Scenario Manager.
2. Click Summary.
3. Click Scenario summary or Scenario PivotTable report.
4. In the Result cells box, enter the references for the cells that refer to cells whose values are changed by the scenarios. Separate multiple references with commas.

 Notes 

• Scenario reports do not automatically recalculate. If you change the values of a scenario, those changes will not show up in an existing summary report, but will show up if you create a new summary report.
• You don't need result cells to generate a scenario summary report, but you do need them for a scenario PivotTable report.

 Top of Page

Excel > What-if analysis

Project values in a series

Excel 2007

Whether you need to forecast expenses for the next year or project the expected results for a series in a scientific experiment, you can use Microsoft Office Excel to automatically generate future values that are based on existing data or to automatically generate extrapolated values that are based on linear trend or growth trend calculations.

You can fill in a series of values that fit a simple linear trend or an exponential growth trend by using the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.) or the Series command. To extend complex and nonlinear data, you can use worksheet functions or the regression analysis (regression analysis: A form of statistical analysis used for forecasting. Regression analysis estimates the relationship between variables so that a given variable can be predicted from one or more other variables.) tool in the Analysis ToolPak Add-in (add-in: A supplemental program that adds custom commands or custom features to Microsoft Office.).

What do you want to do?

Fill in a series for a linear best-fit trend automatically

Fill in a series for a growth trend automatically

Fill in linear trend or growth trend values manually

Calculate trends by adding a trendline to a chart

Project values with a worksheet function

Perform regression analysis with the Analysis ToolPak Add-in

Fill in a series for a linear best-fit trend automatically

In a linear series, the step value, or the difference between the first and next value in the series, is added to the starting value and then added to each subsequent value.

To fill in a series for a linear best-fit trend, do the following:

1. Select at least two cells that contain the starting values for the trend.

If you want to increase the accuracy of the trend series, select additional starting values.

2. Drag the fill handle in the direction that you want to fill with increasing values or decreasing values.

For example, if the selected starting values in cells C1:E1 are 3, 5, and 8, drag the fill handle to the right to fill with increasing trend values, or drag it to the left to fill with decreasing values.

Tip  To manually control how the series is created or to use the keyboard to fill in a series, click the Series command (Home tab, Editing group, Fill button).

 Top of Page

Fill in a series for a growth trend automatically

In a growth series, the starting value is multiplied by the step value to get the next value in the series. The resulting product and each subsequent product are then multiplied by the step value.

To fill in a series for a growth trend, do the following:

1. Select at least two cells that contain the starting values for the trend.

If you want to increase the accuracy of the trend series, select additional starting values.

2. Hold down the right mouse button, drag the fill handle in the direction that you want to fill with increasing values or decreasing values, release the mouse button, and then click Growth Trend on the shortcut menu (shortcut menu: A menu that shows a list of commands relevant to a particular item. To display a shortcut menu, right-click an item or press SHIFT+F10.).

For example, if the selected starting values in cells C1:E1 are 3, 5, and 8, drag the fill handle to the right to fill with increasing trend values, or drag it to the left to fill with decreasing values.

Tip  To manually control how the series is created or to use the keyboard to fill in a series, click the Series command (Home tab, Editing group, Fill button).

 Top of Page

Fill in linear trend or growth trend values manually

When you click the Series command, you can manually control how a linear trend or growth trend is created and then use the keyboard to fill in the values.

• In a linear series, the starting values are applied to the least-squares algorithm (y=mx+b) to generate the series.
• In a growth series, the starting values are applied to the exponential curve algorithm (y=b*m^x) to generate the series.

In either case, the step value is ignored. The series that is created is equivalent to the values that are returned by the TREND function or GROWTH function.

To fill in the values manually, do the following:

1. Select the cell where you want to start the series. The cell must contain the first value in the series.

When you click the Series command, the resulting series replaces the original selected values. If you want to save the original values, copy them to a different row or column, and then create the series by selecting the copied values.

2. On the Home tab, in the Editing group, click Fill, and then click Series.
3. Do one of the following:
   • To fill the series down the worksheet, click Columns.
   • To fill the series across the worksheet, click Rows.
4. In the Step value box, enter the value that you want to increase the series by.

5. Under Type, click Linear or Growth.
6. In the Stop value box, enter the value that you want to stop the series at.

 Note    If there is more than one starting value in the series and you want Excel to generate the trend, select the Trend check box.

 Top of Page

Calculate trends by adding a trendline to a chart

When you have existing data for which you want to forecast a trend, you can create a trendline (trendline: A graphic representation of trends in data series, such as a line sloping upward to represent increased sales over a period of months. Trendlines are used for the study of problems of prediction, also called regression analysis.) in a chart. For example, if you have a chart in Excel that shows sales data for the first several months of the year, you can add a trendline to the chart that shows the general trend of sales (increasing or decreasing or flat) or that shows the projected trend for months ahead.

This procedure assumes that you already created a chart that is based on existing data. If you have not done so, see the topic Create a chart.

1. Click the chart.
2. Click the data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) to which you want to add a trendline (trendline: A graphic representation of trends in data series, such as a line sloping upward to represent increased sales over a period of months. Trendlines are used for the study of problems of prediction, also called regression analysis.) or moving average (moving average: A sequence of averages computed from parts of a data series. In a chart, a moving average smooths the fluctuations in data, thus showing the pattern or trend more clearly.).
3. On the Layout tab, in the Analysis group, click Trendline, and then click the type of regression trendline or moving average that you want.
4. To set options and format the regression trendline or moving average, right-click the trendline, and then click Format Trendline on the shortcut menu.
5. Select the trendline options, lines, and effects that you want.
   • If you select Polynomial, enter in the Order box the highest power for the independent variable.
   • If you select Moving Average, enter in the Period box the number of periods to be used to calculate the moving average.

 Notes 

• The Based on series box lists all the data series in the chart that support trendlines. To add a trendline to another series, click the name in the box, and then select the options that you want.
• If you add a moving average to an xy (scatter) chart, the moving average is based on the order of the x values plotted in the chart. To get the result that you want, you may need to sort the x values before you add a moving average.

 Top of Page

Project values with a worksheet function

Using the FORECAST function  The FORECAST function calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. The known values are existing x-values and y-values, and the new value is predicted by using linear regression. You can use this function to predict future sales, inventory requirements, and consumer trends.

Using the TREND function or GROWTH function  The TREND and GROWTH functions can extrapolate future y-values that extend a straight line or exponential curve that best describes the existing data. They can also return only the y-values based on known x-values for the best-fit line or curve. To plot a line or curve that describes existing data, use the existing x-values and y-values returned by the TREND or GROWTH function.

Using the LINEST function or LOGEST function  You can use the LINEST or LOGEST function to calculate a straight line or exponential curve from existing data. The LINEST function and LOGEST function return various regression statistics, including the slope and intercept of the best-fit line.

The following table contains links to more information about these worksheet functions.

 Top of Page

Perform regression analysis with the Analysis ToolPak Add-in

When you need to perform more complicated regression analysis — including calculating and plotting residuals — you can use the regression analysis tool in the Analysis ToolPak Add-in.

1. On the Add-ins menu, in the Menu Commands group, click Data Analysis.

If the the Add-ins tab or the Data Analysis command is not available, load the Analysis ToolPak.

How to load the Analysis ToolPak

1. Click the Microsoft Office Button , click Excel Options, and then click the Add-ins category.
2. In the Manage list, select Excel Add-ins, and then click Go.
3. In the Add-ins available list, select the Analysis ToolPak box, and then click OK.
4. If necessary, follow the instructions in the Setup program.
2. In the Data Analysis dialog box, select the name of the analysis tool that you want, then click OK.
3. In the dialog box for the tool that you selected, select the analysis options that you want.

You can click the Help button in the dialog box to get more information about the options.

 Top of Page

See Also

• Add, change, or remove a trendline in a chart

Excel > What-if analysis

Define and solve a problem by using Solver

Excel 2007

Solver is part of a suite of commands sometimes called what-if analysis (what-if analysis: A process of changing the values in cells to see how those changes affect the outcome of formulas on the worksheet. For example, varying the interest rate that is used in an amortization table to determine the amount of the payments.) tools. With Solver, you can find an optimal value for a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) in one cell — called the target cell — on a worksheet. Solver works with a group of cells that are related, either directly or indirectly, to the formula in the target cell. Solver adjusts the values in the changing cells that you specify — called the adjustable cells — to produce the result that you specify from the target cell formula. You can apply constraints (constraints: The limitations placed on a Solver problem. You can apply constraints to adjustable cells, the target cell, or other cells that are directly or indirectly related to the target cell.) to restrict the values that Solver can use in the model, and the constraints can refer to other cells that affect the target cell formula.

In this article

Overview

Define and solve a problem

Step through Solver trial solutions

Change how Solver finds solutions

Save or load a problem model

Algorithms and methods used by Solver

Overview

Use Solver to determine the maximum or minimum value of one cell by changing other cells. For example, you can change the amount of your projected advertising budget and see the effect on your projected profit amount.

Example of a Solver evaluation

In the following example, the level of advertising in each quarter affects the number of units sold, indirectly determining the amount of sales revenue, the associated expenses, and the profit. Solver can change the quarterly budgets for advertising (cells B5:C5), up to a total budget constraint of $20,000 (cell F5), until the value for the total profit reaches the maximum possible amount. The values in the adjustable cells are used to calculate the profit for each quarter, so the values are related to the formula in the target cell F7, =SUM(Q1 Profit:Q2 Profit).

Adjustable cells

Constrained cell

Target cell

After Solver runs, the new values are as follows.

 Top of Page

Define and solve a problem

1. On the Data tab, in the Analysis group, click Solver.

If the Solver command or the Analysis group is not available, you need to load the Solver Add-in (add-in: A supplemental program that adds custom commands or custom features to Microsoft Office.) program.

How to load the Solver Add-in program

1. Click the Microsoft Office Button , click Excel Options, and then click the Add-ins category.
2. In the Manage box, click Excel Add-ins, and then click Go.
3. In the Add-ins available box, select the Solver Add-in check box, and then click OK.
2. In the Set Target Cell box, enter a cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.) or name (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) for the target cell. The target cell must contain a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).).
3. Do one of the following:
   1. If you want the value of the target cell to be as large as possible, click Max.
   2. If you want the value of the target cell to be as small as possible, click Min.
   3. If you want the target cell to be a certain value, click Value of, and then type the value in the box.
4. In the By Changing Cells box, enter a name or reference for each adjustable cell. Separate the nonadjacent references with commas. The adjustable cells must be related directly or indirectly to the target cell. You can specify up to 200 adjustable cells.
5. If you want Solver to automatically propose the adjustable cells based on the target cell, click Guess.
6. In the Subject to the Constraints box, enter any constraints (constraints: The limitations placed on a Solver problem. You can apply constraints to adjustable cells, the target cell, or other cells that are directly or indirectly related to the target cell.) that you want to apply.

How to add, change, or delete a constraint

1. In the Solver Parameters dialog box, under Subject to the Constraints, click Add.
2. In the Cell Reference box, enter the cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.) or name (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) of the cell range for which you want to constrain the value.
3. Click the relationship ( <=, =, >=, int, or bin ) that you want between the referenced cell and the constraint (constraints: The limitations placed on a Solver problem. You can apply constraints to adjustable cells, the target cell, or other cells that are directly or indirectly related to the target cell.). If you click int, integer appears in the Constraint box. If you click bin, binary appears in the Constraint box.
4. In the Constraint box, type a number, a cell reference or name, or a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).).
5. Do one of the following:
   • To accept the constraint and add another, click Add.
   • To accept the constraint and return to the Solver Parameters dialog box, click OK.
6. You can apply the int and bin relationships only in constraints on adjustable cells.
7. When the Assume Linear Model check box in the Solver Options dialog box is selected, there is no limit on the number of constraints. For nonlinear problems, each adjustable cell can have up to 100 constraints, in addition to bounds and integer restrictions on the variables.
8. In the Solver Parameters dialog box, under Subject to the Constraints, click the constraint (constraints: The limitations placed on a Solver problem. You can apply constraints to adjustable cells, the target cell, or other cells that are directly or indirectly related to the target cell.) that you want to change or delete.
9. Click Change and then make your changes, or click Delete.
7. Click Solve and do one of the following:
   1. To keep the solution values on the worksheet, click Keep Solver Solution in the Solver Results dialog box.
   2. To restore the original data, click Restore Original Values.

 Notes 

3. You can interrupt the solution process by pressing ESC. Microsoft Office Excel recalculates the worksheet with the last values that are found for the adjustable cells.
4. To create a report that is based on your solution after Solver finds a solution, you can click a report type in the Reports box and then click OK. The report is created on a new worksheet in your workbook. If Solver doesn't find a solution, the option to create a report is unavailable.
5. To save your adjusting cell values as a scenario that you can display later, click Save Scenario in the Solver Results dialog box, and then type a name for the scenario in the Scenario Name box.

 Top of Page

Step through Solver trial solutions

1. After you define a problem, click Options in the Solver Parameters dialog box.
2. In the Solver Options dialog box, select the Show Iteration Results check box to see the values of each trial solution, and then click OK.
3. In the Solver Parameters dialog box, click Solve.
4. In the Show Trial Solution dialog box, do one of the following:
   • To stop the solution process and display the Solver Results dialog box, click Stop.
   • To continue the solution process and display the next trial solution, click Continue.

 Top of Page

Change how Solver finds solutions

1. In the Solver Parameters dialog box, click Options.
2. In the Solver Options dialog box, choose one or more of the following options:

Solution time and iterations

1. In the Max Time box, type the number of seconds that you want to allow for the solution time.
2. In the Iterations box, type the maximum number of iterations that you want to allow.

Degree of precision

3. In the Precision box, type the degree of precision that you want. The smaller the number, the higher the precision.

Integer tolerance

4. In the Tolerance box, type the percentage of error that you want to allow in the solution.

Degree of convergence

5. In the Convergence box, type the amount of relative change that you want to allow in the last five iterations before Solver stops with a solution. The smaller the number, the less relative change is allowed.

 Note    You can click the Help button in the dialog box to get more information about other options.

3. Click OK.
4. In the Solver Parameters dialog box, click Solve or Close.

 Top of Page

Save or load a problem model

1. Click Options.
2. Click Save Model or Load Model.
3. When you save a model, enter the reference for the first cell of a vertical range of empty cells in which you want to place the problem model. When you load a model, enter the reference for the entire range of cells that contains the problem model.

Tip  You can save the last selections in the Solver Parameters dialog box with a worksheet by clicking the Microsoft Office Button , and then by clicking Save. You can also define more than one problem for a worksheet by clicking Save Model to save problems individually.

 Top of Page

Algorithms and methods used by Solver

The Microsoft Office Excel Solver tool uses the Generalized Reduced Gradient (GRG2) nonlinear optimization code, which was developed by Leon Lasdon, University of Texas at Austin, and Alan Waren, Cleveland State University.

Linear and integer problems use the simplex method with bounds on the variables and the branch-and-bound method, implemented by John Watson and Daniel Fylstra, Frontline Systems, Inc. For more information about the internal solution process that is used by Solver, contact:

Frontline Systems, Inc.
P.O. Box 4288
Incline Village, NV 89450-4288
(775) 831-0300
Web site: http://www.solver.com
E-mail: info@solver.com

Portions of the Microsoft Office Excel Solver program code are copyright 1990, 1991, 1992, and 1995 by Frontline Systems, Inc. Portions are copyright 1989 by Optimal Methods, Inc.

 Top of Page

Excel > What-if analysis

Perform statistical and engineering analysis with the Analysis ToolPak

Excel 2007

If you need to develop complex statistical or engineering analyses, you can save steps and time by using the Analysis ToolPak. You provide the data and parameters for each analysis, and the tool uses the appropriate statistical or engineering macro functions to calculate and display the results in an output table. Some tools generate charts in addition to output tables.

The Analysis ToolPak includes the tools described below. To access these tools, click Data Analysis in the Analysis group on the Data tab. If the Data Analysis command is not available, you need to load the Analysis ToolPak add-in program.

Load the Analysis ToolPak

1. Click the Microsoft Office Button , and then click Excel Options.
2. Click Add-ins, and then in the Manage box, select Excel Add-ins.
3. Click Go.
4. In the Add-Ins available box, select the Analysis ToolPak check box, and then click OK.

Tip  If Analysis ToolPak is not listed in the Add-Ins available box, click Browse to locate it.

If you are prompted that the Analysis ToolPak is not currently installed on your computer, click Yes to install it.

For a description of each tool, click on a tool name in the following list.

Anova

• Whether the heights of plants for the different fertilizer brands are drawn from the same underlying population. Temperatures are ignored for this analysis.
• Whether the heights of plants for the different temperature levels are drawn from the same underlying population. Fertilizer brands are ignored for this analysis.

Correlation

Covariance

Descriptive Statistics

Exponential Smoothing

F-Test Two-Sample for Variances

Fourier Analysis

Histogram

Moving Average

• N is the number of prior periods to include in the moving average
• Aj is the actual value at time j
• Fj is the forecasted value at time j

Random Number Generation

Rank and Percentile

Regression

Sampling

t-Test

z-Test

 Notes 

• The data analysis functions can be used on only one worksheet at a time. When you perform data analysis on grouped worksheets, results will appear on the first worksheet and empty formatted tables will appear on the remaining worksheets. To perform data analysis on the remainder of the worksheets, recalculate the analysis tool for each worksheet.
• For a list of books that provide detailed information on statistical methods or algorithms that were used to create the Microsoft Excel statistical tools and functions, see A bibliography of statistical methods and algorithms.

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.

• How do I install the Data Analysis Toolkit from Office Basic CD?
• How can you access Data Analysis Plus for MS Excel?

Excel > What-if analysis

Use Goal Seek to find the result you want by adjusting an input value

Excel 2007

If you know the result that you want from a formula, but are not sure what input value the formula needs to get that result, use the Goal Seek feature. For example, suppose that you need to borrow some money. You know how much money you want, how long you want to take to pay off the loan, and how much you can afford to pay each month. You can use Goal Seek to determine what interest rate you will need to secure in order to meet your loan goal.

 Note    Goal Seek works only with one variable input value. If you want to accept more than one input value; for example, both the loan amount and the monthly payment amount for a loan, you use the Solver add-in. For more information about the Solver add-in, follow the links in the See Also section.

Step-by-step with an example

Let's look at the preceding example, step-by-step.

Because you want to calculate the loan interest rate needed to meet your goal, you use the PMT function. The PMT function calculates a monthly payment amount. In this example, the monthly payment amount is the goal that you seek.

Prepare the worksheet

1. Open a new, blank worksheet.
2. First, add some labels in the first column to make it easier to read the worksheet.
   1. In cell A1, type Loan Amount.
   2. In cell A2, type Term in Months.
   3. In cell A3, type Interest Rate.
   4. In cell A4, type Payment.
3. Next, add the values that you know.
   1. In cell B1, type 100000. This is the amount that you want to borrow.
   2. In cell B2, type 180. This is the number of months that you want to pay off the loan.

 Note    Although you know the payment amount that you want, you do not enter it as a value, because the payment amount is a result of the formula. Instead, you add the formula to the worksheet and specify the payment value at a later step, when you use Goal Seek.

4. Next, add the formula for which you have a goal. For the example, use the PMT function:
   1. In cell B4, type =PMT(B3/12,B2,B1). This formula calculates the payment amount. In this example, you want to pay $900 each month. You don't enter that amount here, because you want to use Goal Seek to determine the interest rate, and Goal Seek requires that you start with a formula.

The formula refers to cells B1 and B2, which contain values that you specified in preceding steps. The formula also refers to cell B3, which is where you will specify that Goal Seek put the interest rate. The formula divides the value in B3 by 12 because you specified a monthly payment, and the PMT function assumes an annual interest rate.

Because there is no value in cell B3, Excel assumes a 0% interest rate and, using the values in the example, returns a payment of $555.56. You can ignore that value for now.

For more information about the PMT function, see the See Also section.

5. Finally, format the target cell (B3) so that it displays the result as a percentage.
   1. On the Home tab, in the Number group, click Percentage.
   2. Click Increase Decimal or Decrease Decimal to set the number of decimal places.

Use Goal Seek to determine the interest rate

1. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Goal Seek.
2. In the Set cell box, enter the reference for the cell that contains the formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) that you want to resolve. In the example, this reference is cell B4.
3. In the To value box, type the formula result that you want. In the example, this is -900. Note that this number is negative because it represents a payment.
4. In the By changing cell box, enter the reference for the cell that contains the value that you want to adjust. In the example, this reference is cell B3.

 Note    The cell that Goal Seek changes must be referenced by the formula in the cell that you specified in the Set cell box.

5. Click OK.

Goal Seek runs and produces a result, as shown in the following illustration.