With your cursor in the “By changing cell” field, click the Collapse Dialog button on the right side of the field and click the cell in the worksheet that holds the formula input you want to change (in this example, that would be cell E3). This is impossible, of course, because cell E4 generates its value by dividing one department’s sales by the total of all departments’ sales, which means the value in cell F3, the grand total, always will be larger than the value in cell E3, one department’s total. If you make this mistake, Goal Seek will attempt to find a value for cell E3 that made it 20 times larger than (or 2000% of) the value in F3. In this case, you might mistakenly type 20 in the “To value” field instead of. When you’re working with percentages in Goal Seek, it’s easy to type in the wrong value. Scenarios are limited to 32 cell changes each, so you might need to create more than one scenario to hold your original values, but the work is worth it to make sure your original data remains intact. If you close your worksheet without restoring the original values by clicking the Undo button, Excel will overwrite the original values! As a second safety measure, create a Normal scenario that contains the original values of every cell affected by one of your scenarios. Here’s why: unless you carefully undo the scenario change, Excel leaves the values from the last-active scenario in place. Instead, make a backup copy of the workbook to use for your scenario what-if analyses.
Warning: never use scenarios in any workbook that holds the only copy of your data. When you want to hide the scenario, just click the Undo button on Excel’s standard toolbar. Click the name of the scenario to display, and click the Show button.Ĭlick Close to close the Scenario Manager dialog box.
0 kommentar(er)
