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
 
Search JabSto ::




Custom Search