excel

1

INTRODUCTION TO ENGINEERING

3 Credit Hours, Fall 2018

Excel Challenge Instructions

 Download the ExcelChallenge_Worksheets.xls file from Blackboard.

 Save the file on your computer with the following file name: ExcelChallenge_Lastname.xls.

 Use this file to complete the following 6 challenges. Be sure to save your file as you work through the challenges.

 When you are finished, upload your file to Blackboard using the provided assignment link.

1) Intro to Formulas Challenge

1. Click the Intro to Formulas tab 2. Create a formula in cell D4 that multiplies the quantity in B4 by the price per unit in

cell C4.

3. Use the fill handle to copy the formula in cell D4 to cells D5:D7. 4. Change the price per unit for the fried plantains in cell C6 to $2.25. Notice that the line

total automatically changes as well. 5. Edit the formula for the total in cell D8 so it also adds cell D7. 6. When you are finished, your workbook should look like Figure 1.

Figure 1: Solution to Intro to Formulas Challenge

2) Creating More Complex Formulas Challenge

1. Click the More Complex Formulas tab

2. In cell D7, create a formula that calculates the tax for the invoice. Use a sales tax rate of 7.5%.

3. In cell D8, create a formula that finds the total for the order. In other words, this formula should add cells D3:D7.

4. In cell D9 create a formula that calculates the total after a 10% discount. If you need help understanding how to take a percentage off of a total, check out the lesson on Discounts, Markdowns, and Sales.

2

5. When you are finished, your spreadsheet should look like Figure 2.

Figure 2: Solution to Creating More Complex Formulas Challenge

3) Relative and Absolute Cell References Challenge

1. Click the Relative and Absolute PI tab

2. In cell D4, enter a formula that multiplies the unit price in B4, the quantity in C4, and the tax rate in E2. Make sure to use an absolute cell reference for the tax rate because it will be the same in every cell.

3. Use the fill handle to copy the formula you just created to cells D5:D12. 4. Change the tax rate in cell E2 to 6.5%. Notice that all of your cells have updated. When you're

finished, your workbook should look like Figure 3.1.

Figure 3.1: Solution to Relative and Absolute Cell References Challenge – Part 1

5. Click the Relative and Absolute P2 tab. 6. Delete the value in cell C5 and replace it with a reference to the total cost of the paper

goods. Hint: The cost of the paper goods is in cell E13 on the Relative and Absolute P1 worksheet.

7. When you are finished, the Relative and Absolute P2 worksheet should look something like Figure 3.2.

3

Figure 3.2: Solution to Relative and Absolute Cell References Challenge – Part 2

4) Functions Challenge

1. Click the Functions tab

2. In cell F3, insert a function to calculate the average of the four scores in cells B3:E3.

3. Use the fill handle to copy your function in cell F3 to cells F4:F17.

4. In cell B18, use AutoSum to insert a function that calculates the lowest score in cells B3:B17.

5. In cell B19, use the Function Library to insert a function that calculates the median of the scores in cells B3:B17. Hint: You can find the median function by going to More Functions > Statistical.

6. In cell B20, create a function to calculate the highest score in cells B3:B17.

7. Select cells B18:B20, then use the fill handle to copy all three functions you just created to cells C18:F20.

8. When you are finished, your workbook should look like Figure 4.

Figure 4: Solution to Functions Challenge

4

5) Tables Challenge

1. Click the Tables tab

2. Select cells A2:D9 and format as table. Choose one of the light styles.

3. Insert a row between rows 4 and 5. In the row you just created, type Empanadas: Banana and Nutella, with a unit price of $3.25, and a quantity of 12.

4. Change the table style to Table Style Medium 14.

5. In Table Style Options, uncheck banded rows and check banded columns.

6. When you are finished, your workbook should look like Figure 5.

Figure 4: Solution to Tables Challenge

6) Page Layout and Printing Challenge

1. Click the Page layout and Printing tab

2. In the Page Layout tab on the Ribbon, use the Print Titles feature to repeat row 1 at the top and column A at the left.

3. Using the Page Break Preview command, move the break between rows 47 and 48 up so it's between rows 40 and 41.

4. In Backstage view, open the Print Pane.

5. In the Print pane, change the orientation to Landscape.

6. Change the margins to Narrow.

7. Change the scaling to Fit All Columns on One Page.

8. When you are finished, your print preview should look like Figure 6.

5

Figure 5: Solution to Page Layout and Printing Challenge