Microsoft Office Tutorials and References

In Depth Information

EX 232

Excel Chapter 3
What-If Analysis, Charting, and Working with Large Worksheets

Make It Right

Analyze a workbook and correct all errors and/or improve the design.

Inserting Rows, Moving a Range, and Correcting Formulas in a Worksheet

Instructions:
Start Excel. Open the workbook Make It Right 3-1 e-MusicPro.com Annual Projected

Net Income. See the inside back cover of this topic for instructions for downloading the Data Files for

Students, or see your instructor for information on accessing the ﬁ les required for this topic. Correct

the following design and formula problems (Figure 3–84a) in the worksheet.

(a) Before

(b) After

Figure 3–84

1. The Royalty in cell C3 is computed using the formula =B9*B3 (Royalties %

Sales). Similar

formulas are used in cells C4 and C5. The formula in cell C3 was entered and copied to cells C4

and C5. Although the result in cell C3 is correct, the results in cells C4 and C5 are incorrect.

Edit the formula in cell C3 by changing cell B9 to an absolute cell reference. Copy the corrected

formula in cell C3 to cells C4 and C5. After completing the copy, click the Auto Fill Options

button arrow that displays below and to the right of cell C5 and choose Fill Without Formatting.

2. The Royalty Bonus amounts in cells D3, D4, and D5 are computed using the IF function. The

Royalty Bonus should equal the amount in cell B10 ($50,000) if the corresponding Sales in

column B is greater than or equal to $2,750,000. If the corresponding Sales in column B is less

than $2,750,000, then the Royalty Bonus is zero ($0). The IF function in cell D3 was entered and