Microsoft Office Tutorials and References

In Depth Information

**Calculate Multiple Solutions to a Formula**

Calculate Multiple

Solutions to a Formula

You can enhance your Excel data analysis by

setting up a worksheet model that automatically

calculates multiple solutions to a formula.

Perhaps the most basic method for analyzing

worksheet data is a technique called
what-if

analysis
. With what-if analysis, you first

calculate a formula D, based on the input from

variables A, B, and C. You then say, “What if I

change variable A? Or B or C? What happens

to the result?”

One way to do this is to set up the worksheet

model and then manually change the formula’s

input cells. For example, if you are calculating

a loan payment, you can enter different interest

rate values to see how this affects the payment.

The problem with this method is that you see

only a single result at one time. If you are

interested in studying the effect a range of

values has on the formula, you need to set up a

data table
. This is a table that consists of the

formula you are using, and multiple input

values for that formula. Excel automatically

creates a solution to the formula for each

different input value.

1
Type the input values you

want.

To enter the values in a

column, you can start the

column one cell down

and one cell to the left of

the cell containing the

formula, as shown here.

To enter the values in a

row, you can start the

row one cell up and one

cell to the right of the

formula.

2
Select the range that

includes the input values

and the formula.

3
Click the Data tab.

4
Click What-If Analysis.

5
Click Data Table.

1

3

4

5

2