Excel 2016 Introduction

Lesson – Creating Simple Formulas

Excel 2016

E XERCISE

C REATING S IMPLE F ORMULAS



T ASK

1. Create and work with formulas.

2. Open Region03.xlsx . 3. In cell B9 , use the AutoSum button to total the sales for Qtr 1.

4. In cell F5 , use the AutoSum button to total the sales for the Northern region. 5. Select the blank cells in the range F6:F8 and use the AutoSum button to total the sales for the three regions at the same time. Check each formula on the formula bar to make sure that columns B through E were calculated for each row. 6. In cell H5 , create a formula that subtracts the expenses in cell G5 from the total sales in cell F5 for the Northern region. 7. In cell I5 , type a function that averages the Northern region sales for the four quarters in the range B5:E5 . 8. In cell I6 , use the AutoSum list to enter a function that averages the Southern region sales for the four quarters in the range B6:E6 . 9. In cell I7 , use the Insert Function button to average the Central region sales for the four quarters in the range B7:E7 . 10. In cell I8 , use any method to average the Western region sales for the four quarters in the range B8:E8 . 11. In cell H1 , use the AutoSum list to find the maximum quarterly sales for all regions (the range B5:E8 ). 12. Use the AutoCalculate feature to verify the answer in cell H1 . 13. Use the AutoCalculate feature to find the sum of all sales (the range B5:E8 ). 14. In cell C14 , create a formula that calculates an increase of 15% on the total sales in cell B9 . (Hint : Try multiplying cell B9 by 1.15. Refer to the formula in cell C13 if you need an example. ) 15. In cell C15 , create a formula that calculates an increase of 20% on the total sales in cell B9 . ( Hint: Try multiplying cell B9 by 120%. ) 16. Close the workbook without saving it.

Page 124

OFFICEPRO, Inc.