Computer Technology II 

Demonstration - Excel 3 - Part 2 USOE Standard(s): Standard 2 Objective 4 The purpose of this assignment is to review previously learned Excel concepts, practice various logical, math & trig, and text functions including: if, sumif, countif, averageif, right, left mid, trim, upper, lower, and concatenate. 1. 2. 3. 4.

Open “Excel 3 In Class Activity Data” from the EX 3 Data Files folder in the shared drive. Save your file as “Lastname Firstname P# Excel 3 Demo Part 2” to your Excel folder in your student drive. On the If Practice sheet, add a header with your name, filename, period. Switch to normal view.

Romeo & Juliet Ticket Sales 5. Name a range: highlight C5 to C8 (prices)—Name this range “price”. (Formulas tab, Defined Names group, Define Name). Make sure the Name is Price. Click OK. Notice that in the Name box, Price appears. Click in A12, go to the name box and click on the arrow—choose Price—notice that C5:C8 is selected. 6. Calculate an average of the Price range—Click in C10 type “=average(Price)” press Enter. The Average of Price should be $4.25 7. Create an If Function in cell E5 that will calculate the profit—Profit is based on if the number of tickets is over 300 then the profit is 75% of the Price, otherwise the profit is 50% of the price. Click in E5--Go to the Formulas tab, Logical button and choose IF Logical Test—B5>300 Value if True—C5*75%*B5 Value if False—C5*50%*B5 If calculated correctly, the result should be $1,299.00 8. Fill the formula to E8 and format the numbers with Accounting format. 9. Apply Conditional Formatting by selecting B5:B8, Conditional Formatting, Highlight Cells Rules: if the Quantity is greater than 300— apply Red Text to the quantities over 300. 10. Save the file.

1

FBLA Party Budget 11. Place an IF function in K5 that will return the word “Over Budget” if they spent more money than was allotted—The cell should remain blank if they spent within the budgeted amount. Click in cell K5- Formulas tab, Logical, IF Type the following information in the Function Arguments box: Logical Test—J5250,”Goal Met”, “”) the result should be Goal Met 19. Fill the formula for the remaining departments and center the text in E24:E30. 20. Select cells B24:B30 and apply Conditional Formatting, Icon Sets, More Rules… to show a green dot next to the cells that are $250 or more and a red light for those less than $250-change the yellow to red.

21. Save the file.

3

Big Mountain Tours 22. Click on L20. Insert a cell to the left of L20 (Home tab, Cells group, Insert, Insert Cells, Shift cells right) and type Average. 23. Calculate the Average in cell L21 of the number of people signed up for each activity. =AVERAGE(H21:J21) Fill down to row 25. Format with Comma style, zero decimal places. 24. Calculate Totals for K21 through K25- and H:27:K27 - use the Sum function. 25. Select H20:H25 and Insert cells (Shift cells right). 26. Type “Max” for maximum people in cell H20. 27. Fill in the appropriate maximum capacity for each of the tours shown below: Mountain Bike=20, River Raft=40, Bus=15, Cruise=150, Fishing=60 28. In N21, create an IF function for each of the activities that displays “Over” if the Average is over the listed Max; otherwise show nothing:

The cell should show nothing. 29. Fill the formula for the remaining tours. Save. 30. Apply an Orange, Gradient Data Bar Conditional Formatting to the cells L21:L25. (Conditional Formatting, Data Bars, Gradient Fill, Orange Data Bar) 31. Ignore all errors (little green triangles). (Click on the green triangle, click on yellow error box and click on Ignore Errors) 32. Set up with the following: Landscape, narrow margins, print gridlines, fit to one page for Width and Height, center the sheet vertically and horizontally (Page Layout, Page Setup group, Margins, Custom Margins). 33. Re-save. Leave file open for the next activity.

Formula Practice-Fabrikam Sales

1. With “Lastname Firstname P# Excel 3 Demo Part 2” open, click on the File tab, Open and click on the file named “Fabrikam Sales” in the EX 3 Data Files folder in the shared drive. 2. Move Sheet 1 to the workbook “Lastname Firstname P# Excel 3 Demo Part 2”. (Right click on the Sheet Tab, choose Move or Copy, click on the drop down arrow in the To book: window-choose the correct file and then choose move to end). 3. Rename Sheet 1 “Fabrikam”-change the tab color to Standard Purple using the Home tab, Cells group, Format, Tab color (or right-click sheet tab, Tab Color). 4. Autofit columns B, C and D and change the width of column A to 25. 5. Switch to the If Practice sheet. Change the Tab Color to Standard Colors, Light Blue. 6. Switch back to the Fabrikam sheet and insert two columns between columns A and B. 7. Label cell B4 First and C4 Last. 8. Type the name Richard in cell B5 and David in cell B6-notice that Excel wants to Flash Fill the remaining names. Press Enter as soon as David appears and all names will fill automatically. 4

9. Flash Fill the Last names. 10. Switch the columns so Last is in A and First is in B (Cut cells C4:C17-Right Click on A4 and choose Insert Cut Cells). Now repeat for First names and place in B4. 11. Move cell C17 “Total Sales” to A17. 12. Delete column C. 13. Resize column A and B to a width of 15. 14. Save. SumIf 15. In cell D20 place a formula that will add the Sales for all properties that are over $200,000. Use SumIf (Use the Formulas tab, Math & Trig, scroll and choose SumIf-the Function Arguments dialog box opens with text boxes for the arguments-Move the box to the right so the Sale Price column can be seen. -Click in the Range box and then select cells D5:D16 -Click in the Criteria box and type >200000 (notice no commas) Press Tab -Sum Range is optional-we will not use it this time -Click OK-The result should be $1,657,100

16. Click cell D21 17. Choose Recently Used in the Function Library. Choose SumIf. 18. In the Range box, select cells F5:F16 In the Criteria box, type =60 press Tab Criteria_range2, select F5:F16 Criteria2 box type >=5% press Tab and the result should be 2 (at bottom of screen)– click OK. Average 34. Select D27. Click on More Functions, Statistical and choose Average-choose C5:C16 and click OK-the result should be $232,792. 35. Save AverageIf 36. Select D28 37. Click Insert Function and select AverageIf or search for it and select it In the Range box, select cell C5:C16 In the Criteria box, type >=200000 In the Average_range box select G5:G16 Click OK, the result should be 63.33 38. Save AverageIfs 39. Select cell D29 and then go to the Insert Function button, choose AverageIfs. 40. In the Average_range box, select the cells G5:G16 In the Criteria_range1 box, select cells C5:C16 In the Criteria1 box, type 500) Value if True: The service charge amount is calculated by multiplying the regular service charge rate (C14) times the Current Balance ($C$14*F4) . (Use the F4 key to put the $ on the absolute reference $C$4). Value if False: The service charge amount is calculated by multiplying the discount service charge rate (C13) times the Current Balance ($C$13*F4). Hint: If the formula was completed correctly, Dan Costa has a Service Charge Amount of $1.17. 12. Fill the formula from G4 without formatting down to row 10. 13. In Cell I4 set up an IF statement that will insert the words Yes or No in the Late Fee column. If no payment was made by the customer, then a late fee will be applied so the word Yes should be displayed in the cell. Otherwise, the word No should be displayed in the cell. Logical Test: If the Payment is greater than 0. (D4>0) Value if True: “No” Value if False: “Yes” 14. Fill the formula for Late Fee down to row 10. 15. In Cell J4 set up an IF statement that calculates the balance after the late fee. If no payment was made by the customer, then add $25 (C15) to the new balance. Otherwise, the new balance will remain unchanged. Logical Test: If the Late fee is “Yes” (I4=”Yes”). Value if True: The new balance plus the Flat Fee (H4+$C$15). Value if False: The new balance (H4).

16. Copy the formulas (but not the $ signs) in Cells F4:I4 down to the remaining rows. Adjust the column width to show the Total (Autofit). 17. Save. 18. Set up Conditional Formatting for B4:B10. If the Beginning Balance is greater than $500, fill the cell with a light green fill color, dark green text.  Highlight the cells B4 to B10  Go to the Home Tab, Styles Group, Choose Conditional Formatting-choose Highlight Rules-Greater Than—Type 500 in the first cell and choose Green Fill with Dark Green Text. 10

19. Add Orange Gradient Data bars—Conditional Formatting to cells J4:J10. 20. Add a comment to I3 that says Late fee added if payment not made. (Click in I3, go to Review, New Comment) 21. Add a comment to G3 that says Discount Rate or Regular Rate charged based on Beginning Balance. 22. Add a comment to B3 that says: Green highlights for accounts over $500. 23. Click Show All Comments to hide the comment boxes. 24. Add an Online Image of a black drawing of a bicycle shown below to the right side of Title— re-size height to .75 inches. Make a copy of the image and place on the left of the title-flip the image horizontally. (Format, Rotate, Flip Horizontal)

25. Change the alignment for the sub-title A2:J2 to Center Across Selection so you can type in the individual cells— Click on Subtitle, Unmerge the cells by clicking on the Merge and Center button, click on the dialog box launcher in the Alignment group. In the Alignment Tab, Text alignment, Horizontal, choose Center Across Selection. 26. Click on Cell J2—Type “Website”, change the font to size 10. Insert a hyperlink in J2—On the Insert Tab, choose Hyperlink, in the address window, type www.sunshinecycle.com Test the hyperlink. 27. Save and keep it open. 28. 29. 30. 31. 32. 33. 34. 35. 36. 37. 38. 39. 40. 41.

Open the Cycle Append file from the EX 3 Data Files folder in the shared drive. Insert a column before column C. Label C1 Customer. Combine the Last and First names in column C using Concatenate with a comma and a space before the First Name. (=Concatenate(A2,”, “,B2) Use the Fill handle for all names. Move Sheet 1 to the “Lastname Firstname P# Excel 3 Demo Part 2” -place the sheet after Sunshine Cycle and name the tab “Cycle Append”. On the Sunshine Cycle tab, Insert 7 rows above Row 5 (select rows 5-11; right-click on row number, Insert). On the Cycle Append tab highlight and copy cells C2:G8. On the Sunshine Cycle tab, in Cell A5, use Paste Special and Paste Values-(otherwise the names will give you a #Ref error). (right-click, Paste Special, click on Values) Change all number formats to match the others using Format Painter. (Highlight B12:E12 and use Format Painter, paste the format from B5:J11) Fill formulas from F4:J4 for the new accounts –adjust the formatting for cells F5:J11 to Currency style with no $. (click on Number group dialog box launcher, Currency, Symbol, None) Sort the data on the Customer column-do not sort the Total row. Adjust formatting where needed. Save.

Formulas 42. In cell B24 use the Count function to calculate the number of accounts (B4:B17). 43. In cell B25 calculate the number of accounts past due-those accounts where no payment was made-Use CountIf: (Formulas tab, Insert Function, type countif-search and select: Range D4:D17, Criteria =0 press Tab-The result should be 4, click OK.

11

44. In B26 use the SumIf function to calculate a total of New Balances on accounts where no payment was made:

45. In cell B27, calculate the total of late fees by multiplying the Number of Past Due Accounts in C25 by the Late Fee amount in cell C22. =C25*C22 (It should be $100) 46. In cell B28 calculate the Number of Current Accounts using CountIf. Don’t count the Late Fee column because CountIf will only count numbers. Count the Payment column and look for >0 for accounts that made payments. The result should be 10.

47. In cell B29, calculate the Total on Current Accounts using SumIf. The result should be 3420.66.

48. 49. 50. 51.

Format C26, C27 and C29 with Currency style. Save. Change the orientation to landscape and set margins to narrow. Center the spreadsheet horizontally and vertically on the page (Page Layout tab, Page Setup group, Margins, Custom margins). 52. Set the document to print one page wide by one page tall and to print gridlines and headings. 53. Re-save the file. 54. Delete the Cycle Append sheet.

Backstage View Set the AutoRecover save feature to save backup files to your H drive. 

Go to the File Tab, choose Options, choose the Save button on the left. Change the AutoRecover file location to H:\. Click on OK to exit out of Excel Options.

Set Excel to change the word typed “alot” to the words “a lot”. This is done on AutoCorrect.  

Go to the File Tab, choose Options, choose Proofing, click on the AutoCorrect Options button and in the Replace window type “alot” and in the With window type “a lot”. Try it out in an empty cell.

Copy the “Lastname Firstname P# Excel 3 Demo Part 2” to the student dropbox. There is not a grading sheet for this demonstration.

12