Microsoft Office Tutorials and References
In Depth Information
Playing What-If with Goal Seeking
2. Click Data What-If Analysis Data Table on the Ribbon.
Excel opens the Data Table dialog box with the insertion point in the
Row Input Cell text box.
3. Click cell B4 to enter the absolute cell address, $B$4, in the Row Input
Cell text box.
4. Click the Column Input Cell text box and then click cell B3 to enter
the absolute cell address, $B$3, in this text box.
5. Click OK to close the Data Table dialog box.
Excel fills the blank cells of the data table with a TABLE formula using B4
as the Row Input Cell and B3 as the Column Input Cell.
6. Click cell B7, then click the Format Painter command button in the
Clipboard group on the Home tab and drag through the cell range
C8:F17 to copy the Accounting number format with no decimal places
to this range.
This Accounting number format is too long to display given the current
width of columns C through F — indicated by the ###### symbols. With
the range C8:F17 still selected from using the Format Painter, Step 7 fixes
this problem.
7. Click the Format command button in the Cells group of the Home tab
and then click AutoFit Column Width on its drop-down menu.
The array formula {=TABLE(B4,B3)} that Excel creates for the two-variable
data table in this example specifies both a Row Input Cell argument (B4) and
a Column Input Cell argument (B3). (See the nearby “Array formulas and the
TABLE function in data tables” sidebar.) Because this single array formula
is entered into the entire data table range of C8:F17, any editing (in terms of
moving or deleting) is restricted to this range.
Playing What-If with Goal Seeking
Sometimes when doing what-if analysis, you have a particular outcome in
mind, such as a target sales amount or growth percentage. When you need
to do this type of analysis, you use Excel’s Goal Seek feature to find the input
values needed to achieve the desired goal.
To use the Goal Seek feature located on the What-If Analysis button’s
dropdown menu, you need to select the cell containing the formula that will
return the result you’re seeking (referred to as the set cell in the Goal Seek
dialog box). Then indicate the target value you want the formula to return
as well as the location of the input value that Excel can change to reach this
target.
Search JabSto ::




Custom Search