Fluent Essays

follow instructions EXCEL PRACTICAL EXERCISE IF function Note: If the function or method was not covered in class or in the textbook, it should not be part of your answer. DO NOT go to the internet for solutions. You are the troop leader for a Girl Scout Troop and it Cookie Sales Time. You want to keep track of how well your troop did with the sales so that you can give them a prize. Instructions: 1. Save the workbook as yourname_P5 2. Enter the data below into an Excel Spreadsheet. Be sure to keep the same columns and rows. A B C D E F G H 1 First Name Last Name Months in Troop Chal et Samoa Thin Mints Peanut Butter Tagalongs 2 Jacqueline LaMar 48 5 8 12 16 5 3 Mariah Parr 52 6 7 13 14 3 4 Rachel Friend 18 7 5 19 13 6 5 Sharon Ames 6 2 5 21 11 4 6 Susie Kwon 12 9 6 14 11 9 7 Hannah Evans 33 4 2 13 9 8 8 Eliza Doolittle 17 2 7 9 12 3 9 Rebecca Lun 22 3 6 16 11 5 10 Marjorie Smith 37 4 2 11 15 8 3. Enter the row title TOTAL at cell A11 4. Enter the column title TOTAL at cell I1 5. Enter the column title PRIZE at cell J1 6. Insert 2 blank lines above the column titles 7. Enter the title: Girl Scout Cookie Sales 8. Enter the subtitle: Troop JR309DL CALCUATIONS 9. Use the SUM function to find the Totals for each type of cookie sold a. Be sure to use the fill handle to copy the formula 10. Use the SUM function to find the Totals for the amount each Girl Scout sold a. Be sure to use the fill handle to copy the formulas 11. Enter the following row titles: a. A15: Prize i. Make it bold b. A16: T-Shirt c. A17: No Prize Continued on the next page IF FUNCTION 12. Show the Prize each Girl Scout has earned. a. You MUST use the IF Function and use Absolute Reference to find the Prize for each of the Girl Scouts i. USE the fill handle to copy the formula to all the other cells in the column b. If she sold more than 45 boxes of cookies, she gets a t-shirt (A16) c. Anything Less, she gets No Prize (A17) FORMATTING 13. Center the Title and Subtitle across the table a. Format them to your liking 14. Place a BLUE colored Double Line Border around all the cells in the table 15. Center all of the Column Titles 16. Center all of the cells that contain numbers 17. Format the rest of the table to your liking. a. Don’t just place the data into the table 18. Change the page orientation to Landscape 19. Adjust column widths as necessary 20. Name the sheet COOKIE SALES and color the tab BLUE CHARTING 21. Create a 3-D Pie Chart that shows the totals for each type of cookie sold 22. Move the Graph to a new sheet a. Call the new sheet Total Sales b. Add a title to the Chart i. You create one c. Add Data Labels to show amount sold (no percent) d. Color the TAB RED FINISHING TOUCHES 23. Add your name and student ID number to the header 24. Re-Order the tabs so that the chart is the 2nd tab 25. Change the document properties - Author, Subject and TAGS (keywords – use a minimum of 3 words separated by commas). o Tags should NOT be your initials, Excel, exercise, practical… Excel Practical - Exercise 6 IF and NESTED IF Statements Note: If the function or method was not covered in class or in the textbook, it should not be part of your answer. DO NOT go to the internet for solutions. You will be constructing a spreadsheet that you can use to calculate your current Grade Point Average. (GPA) 1. Copy the data from the table below into a new Excel Spreadsheet. Keep the data as indicated on the columns and rows. A B C 1 Subject Current Grade Grade Values 2 Math 3 Science 4 English 5 PE 6 Health 7 Computers 8 Social Studies 9 10 Total Values = 11 12 My GPA = 13 14 Grade Grade Value 15 A 4 16 B 3 17 C 2 18 D 1 19 F 0 2. Save the document as yourname_P6 3. Add 2 rows at the top of the table 4. Enter the title: GPA Calculator a. Center the title across the table in row 1 5. In the column titled, Current Grade, enter grades to be calculated a. Here are some examples of grades you can try: i. Math: A Science: B English: A PE: C Health: B Computers: B Social Studies: B ii. Math: C Science: B English: C PE: C Health: A Computers: A Social Studies: B iii. Math: A Science: C English: A PE: B Health: A Computers: C Social Studies: C iv. Math: A Science: A English: A PE: D Health: C Computers: B Social Studies: C 6. Use a Nested-IF Function to create the Grade Values. Use the Grade and Values table to help you. You will need to use a NESTED IF statement.  Condition: IF Current Grade = “A”, then 4.0, else IF Current Grade = “B”, then 3.0, else IF Current Grade = “C”, then 2.0, else IF Current Grade = “D”, then 1.0, else IF Current Grade is anything else it should be 0.  Copy the formula to ALL cells in the column o Be sure that ALL cells in the column have the SAME formula This video may help you: https://www.youtube.com/watch?v=gYxpnmjoQds 7. In the cell next to the TOTAL VALUES=, insert a formula that calculates the Total of all individual letter grade values found in the Column C. a. You MUST use a FUNCTION and use ABSOLUTE CELL reference based on the points in the Grade Value column 8. In the cell next to the MY GPA= cell, insert a formula that calculates the AVERAGE of all the individual Grade Values. a. You MUST use a FUNCTION b. SHOW 2 decimal places 9. In the same cell as above, use CONDITIONAL FORMATTING to color the cell Green with a Bold White Font if the GPA is greater than 3.00 GPA. 10. Under the cell that calculates MY GPA, Insert an IF Statement to deliver one of the following messages: If a students GPA is greater than or equal to a 3.50, then have the cell display HONOR ROLL! If the students GPA is less than the 3.50, have the cell display, Keep Trying! FINISHING TOUCHES 11. Format the table to your liking 12. Change the tab name to GPA Calculator a. Color the tab GREEN 13. Add your name and student ID number to the header 14. Change the document properties - Author, Subject and TAGS (keywords – use a minimum of 3 words separated by commas). o Tags should NOT be your initials, abc, 123, or the words “Excel”, “exercise”, “practical”, … https://www.youtube.com/watch?v=gYxpnmjoQds