Excel Exam
doudou
CMRExcelExam.xlsmLogDetails
| Name | Time/Date |
| maysta | 6/4/18 9:41 |
| maysta | 6/4/18 9:41 |
| maysta | 6/4/18 9:42 |
| maysta | 6/4/18 9:42 |
Rules For Exam
| CMR 181 - Rules For Exam |
| What are the rules for the exams? |
| a. This is an open book, open notes exam. Use anything and everything that you like either in paper or electronic form. |
| b. You may use the computer’s “Help” feature, including the “Office Assistant” and Wizards. |
| c. You may NOT seek or accept help from any other person including students of this course from current or previous terms. |
Functions, Formulas, Chart
| Functions, Formulas, and a Chart | |||
| Enter formulas in the yellow shaded cells. | Data to use for the formulas to the left | ||
| 14 | |||
| Average | 6 | ||
| Sum | 8 | ||
| Min | 88 | ||
| Max | 35 | ||
| Count | 6 | ||
| 45 | |||
| 7 | |||
| 87 | |||
| Complete the following table | |||
| Percent of Total | |||
| Division | Amount | Percent of total | |
| North | $ 12,875.00 | ||
| South | $ 23,984.00 | ||
| East | $ 22,376.00 | ||
| West | $ 18,923.00 | ||
| Total | $ 78,158.00 | ||
| Explain the percent of total table. What does it tell us? | |||
| Create a pie chart that shows the percent of total for each division. Be sure to include the percentages for each division in your chart. Format the chart to something other than the default. | |||
Range Names
| Range Names | ||||||
| Create a range name for each column in the table below. | ||||||
| First Name | Last Name | City | State | Zip | GPA | Year |
| Rahul | Orozco | Frisco | TX | 75034 | 2.5 | 1 |
| Madalynn | Pringle | Rancho Cucamonga | CA | 91730 | 2.7 | 1 |
| Aline | Ho | Moreno Valley | CA | 92553 | 2 | 1 |
| Annalise | Leroy | Vancouver | WA | 98660 | 3 | 3 |
| Princess | Montoya | Peoria | IL | 61602 | 2 | 2 |
| Anana | Dehart | Billings | MT | 59101 | 3.8 | 2 |
| Colette | Burt | Bridgeport | CT | 6604 | 2.3 | 3 |
| Amya | Breedlove | Charlotte | NC | 28202 | 3 | 4 |
| Jean | Brock | North Las Vegas | NV | 89030 | 2.3 | 1 |
| Ita | Jose | Wichita | KS | 67202 | 2.5 | 2 |
| Andres | Palma | South Bend | IN | 46601 | 3.5 | 4 |
| Jovanni | Salazar | Elizabeth | NJ | 7201 | 1.3 | 3 |
| Elden | Bull | Santa Clara | CA | 95050 | 3 | 4 |
| Create formulas below using the range names you created for the table above. | ||||||
| Average GPA | ||||||
| Maximum GPA | ||||||
| Minimum GPA | ||||||
| Create a bar chart that shows GPA. Use the student's last name for the axis labels. | ||||||
PMT Function
| PMT Function | ||
| CREATE the formulas to calculate the yellow shaded cells. These include | ||
| Monthly Payment, Total Loan Payments, and Total Interest Paid. | ||
| Next, compare the loans in the space provided. | ||
| Loan Parameters | ||
| Loan 1 Annual Interest Rate | 6% | |
| Loan 1 Principal | $ 15,000.00 | |
| Loan 1 Term in Months | 12 | |
| Loan 2 Annual Interest Rate | 8% | |
| Loan 2 Principal | $ 15,000.00 | |
| Loan 2 Term in Months | 18 | |
| Loan 1 | Loan 2 | |
| Monthly Payment | Monthly Payment | |
| Total Loan Payments | Total Loan Payments | |
| Total Interest Payments | Total Interest Payments | |
| In the space provided, compare the two loans. Which one is better and why? | ||
Formatting
| Formatting | |||||||||||
| Format A6:E19 to look like the Model below it. You do NOT need to enter any formulas. | |||||||||||
| Note that colors do not need to be identical. | |||||||||||
| Orders Summary | |||||||||||
| Last Name | First Name | Phone Number | Last Order Date | Order Amount | |||||||
| Moon | Betty | 555-1211 | 2/25/13 | 4325.43 | |||||||
| Woodard | Karen | 555-1221 | 2/12/13 | 234.99 | |||||||
| Desai | Russell | 555-1213 | 2/25/13 | 654.4 | |||||||
| Wallace | Anne | 555-1219 | 2/5/13 | 23.77 | |||||||
| Lawrence | Sara | 555-1217 | 2/12/13 | 2377.98 | |||||||
| Griffin | Steven | 555-1216 | 2/5/13 | 1485.12 | |||||||
| Dougherty | Raymond | 555-1214 | 2/10/13 | 754.88 | |||||||
| Powers | Marsha | 555-1218 | 2/25/13 | 10.01 | |||||||
| May | Clyde | 555-1212 | 2/5/13 | 87.54 | |||||||
| Steele | Gloria | 555-1220 | 2/25/13 | 291.56 | |||||||
| Teague | Tina | 555-1215 | 2/27/13 | 7612.11 | |||||||
| Total | 17857.79 | ||||||||||
| Model | |||||||||||
| Orders Summary | |||||||||||
| Last Name | First Name | Phone Number | Last Order Date | Order Amount | |||||||
| Moon | Betty | 555-1211 | Monday, February 25, 2013 | $ 4,325 | |||||||
| Woodard | Karen | 555-1221 | Tuesday, February 12, 2013 | $ 235 | |||||||
| Desai | Russell | 555-1213 | Monday, February 25, 2013 | $ 654 | |||||||
| Wallace | Anne | 555-1219 | Tuesday, February 05, 2013 | $ 24 | |||||||
| Lawrence | Sara | 555-1217 | Tuesday, February 12, 2013 | $ 2,378 | |||||||
| Griffin | Steven | 555-1216 | Tuesday, February 05, 2013 | $ 1,485 | |||||||
| Dougherty | Raymond | 555-1214 | Sunday, February 10, 2013 | $ 755 | |||||||
| Powers | Marsha | 555-1218 | Monday, February 25, 2013 | $ 10 | |||||||
| May | Clyde | 555-1212 | Tuesday, February 05, 2013 | $ 88 | |||||||
| Steele | Gloria | 555-1220 | Monday, February 25, 2013 | $ 292 | |||||||
| Teague | Tina | 555-1215 | Wednesday, February 27, 2013 | $ 7,612 | |||||||
| Total | $ 17,858 |
Kentucky
| Formulas on Multiple Sheets KENTUCKY | |||
| Write formulas for yellow cells on sheets Kentucky, Ohio, and Combined. The combined sheet must use 3D cell references. | |||
| Your formulas must use cell references, not numbers. | |||
| Hint: To calculate the student teacher ratio, divide the number of students by the number of teachers. | |||
| KENTUCKY | |||
| Major | Number of Students | Number of Teachers | Student/ Teacher Ratio |
| Art | 50 | 3 | |
| Business | 600 | 24 | |
| English | 75 | 8 | |
| Geology | 19 | 6 | |
| History | 260 | 26 | |
| Music | 22 | 4 | |
| Total | |||
Ohio
| Formulas on Multiple Sheets Ohio | |||
| Write formulas for yellow cells on sheets Kentucky, Ohio, and Combined. The combined sheet must use 3D cell references. | |||
| Your formulas must use cell references, not numbers. | |||
| Hint: To calculate the student teacher ratio, divide the number of students by the number of teachers. | |||
| OHIO | |||
| Major | Number of Students | Number of Teachers | Student/ Teacher Ratio |
| Art | 7,000 | 72 | |
| Business | 1,200 | 90 | |
| English | 500 | 69 | |
| Geology | 900 | 55 | |
| History | 3,500 | 48 | |
| Music | 350 | 24 | |
| Total | |||
Combined
| Formulas on Multiple Sheets COMBINED | |||
| You must use 3D cell references to use the data from the Kentucky and Ohio pages. | |||
| Your formulas must use cell references, not numbers. | |||
| Note: For cell D19, you need to use a similar formula as above to calculate the overall student/teacher ratio | |||
| COMBINED Kentucky plus Ohio | |||
| Major | Number of Students | Number of Teachers | Student/ Teacher Ratio |
| Art | |||
| Business | |||
| English | |||
| Geology | |||
| History | |||
| Music | |||
| Total | |||
| Complete the following using the appropriate function | |||
| Max | |||
| Min | |||
| Average | |||
| Create a line chart that includes each major's number of students and number of teachers. | |||
| Create a pie chart that shows the proportion of students in each major. | |||
Sort and Filter
| Sort and Filter | |||
| Sort the following data, with a first level sort of Customer alphabetically, and a second level sort pf invoice amount, from largest to smallest. | |||
| Customer | Tracking Number | Invoice Amount | Delivered? |
| Maecenas Ornare Egestas Corporation | 50159 | $ 6,949 | No |
| Gravida Company | 23401 | $ 5,780 | Yes |
| Et Corp. | 17718 | $ 6,031 | No |
| Donec Feugiat Foundation | 87676 | $ 3,688 | Yes |
| Donec Est Nunc PC | 61031 | $ 6,877 | No |
| Dolor Sit Amet Foundation | 23777 | $ 7,698 | No |
| Ullamcorper Duis At Corporation | 28378 | $ 5,501 | Yes |
| Mattis LLP | 56803 | $ 6,929 | Yes |
| Sed Incorporated | 64511 | $ 3,808 | No |
| Dictum Associates | 32937 | $ 6,098 | Yes |
| Enim Corp. | 89409 | $ 7,102 | Yes |
| Vel Arcu Industries | 22196 | $ 6,684 | No |
| Eu LLC | 26034 | $ 5,241 | No |
| Arcu Imperdiet Corp. | 13619 | $ 3,511 | No |
| Lorem Vitae Odio Ltd | 54451 | $ 5,516 | No |
| Fringilla Ornare Consulting | 88653 | $ 3,256 | Yes |
| Amet Dapibus Associates | 56670 | $ 5,612 | No |
| Varius Nam Limited | 19754 | $ 6,918 | No |
| Aliquam Enim Nec Industries | 62758 | $ 4,957 | Yes |
| Primis LLP | 60998 | $ 3,664 | No |
| Sociis Limited | 13815 | $ 6,665 | No |
| Nam Ac Associates | 29843 | $ 3,005 | Yes |
| Scelerisque LLP | 78481 | $ 5,376 | No |
| Donec Est Company | 63382 | $ 4,068 | Yes |
| Phasellus Nulla Integer Company | 15197 | $ 6,803 | Yes |
| Donec Sollicitudin Adipiscing Industries | 14725 | $ 5,144 | No |
| Mauris Incorporated | 79386 | $ 7,750 | No |
| Facilisis LLC | 81682 | $ 5,556 | Yes |
| Pede Ultrices A Incorporated | 53178 | $ 4,554 | No |
| Feugiat Metus Company | 68332 | $ 7,034 | No |
| Nisl PC | 68364 | $ 3,972 | No |
| Non Massa Non Inc. | 75763 | $ 3,027 | Yes |
| Adipiscing Non Associates | 71440 | $ 4,195 | No |
| Enim Nisl LLP | 70305 | $ 5,126 | No |
| Id Erat Etiam Limited | 81246 | $ 6,363 | No |
| Filter he following table. Show only those rows with invoice amounts between $3000 and $5000 | |||
| Customer | Tracking Number | Invoice Amount | Delivered? |
| Maecenas Ornare Egestas Corporation | 50159 | $ 6,949 | No |
| Gravida Company | 23401 | $ 5,780 | Yes |
| Et Corp. | 17718 | $ 6,031 | No |
| Donec Feugiat Foundation | 87676 | $ 3,688 | Yes |
| Donec Est Nunc PC | 61031 | $ 6,877 | No |
| Dolor Sit Amet Foundation | 23777 | $ 7,698 | No |
| Ullamcorper Duis At Corporation | 28378 | $ 5,501 | Yes |
| Mattis LLP | 56803 | $ 6,929 | Yes |
| Sed Incorporated | 64511 | $ 3,808 | No |
| Dictum Associates | 32937 | $ 6,098 | Yes |
| Enim Corp. | 89409 | $ 7,102 | Yes |
| Vel Arcu Industries | 22196 | $ 6,684 | No |
| Eu LLC | 26034 | $ 5,241 | No |
| Arcu Imperdiet Corp. | 13619 | $ 3,511 | No |
| Lorem Vitae Odio Ltd | 54451 | $ 5,516 | No |
| Fringilla Ornare Consulting | 88653 | $ 3,256 | Yes |
| Amet Dapibus Associates | 56670 | $ 5,612 | No |
| Varius Nam Limited | 19754 | $ 6,918 | No |
| Aliquam Enim Nec Industries | 62758 | $ 4,957 | Yes |
| Primis LLP | 60998 | $ 3,664 | No |
| Sociis Limited | 13815 | $ 6,665 | No |
| Nam Ac Associates | 29843 | $ 3,005 | Yes |
| Scelerisque LLP | 78481 | $ 5,376 | No |
| Donec Est Company | 63382 | $ 4,068 | Yes |
| Phasellus Nulla Integer Company | 15197 | $ 6,803 | Yes |
| Donec Sollicitudin Adipiscing Industries | 14725 | $ 5,144 | No |
| Mauris Incorporated | 79386 | $ 7,750 | No |
| Facilisis LLC | 81682 | $ 5,556 | Yes |
| Pede Ultrices A Incorporated | 53178 | $ 4,554 | No |
| Feugiat Metus Company | 68332 | $ 7,034 | No |
| Nisl PC | 68364 | $ 3,972 | No |
| Non Massa Non Inc. | 75763 | $ 3,027 | Yes |
| Adipiscing Non Associates | 71440 | $ 4,195 | No |
| Enim Nisl LLP | 70305 | $ 5,126 | No |
| Id Erat Etiam Limited | 81246 | $ 6,363 | No |
VLOOKUP Function
| VLOOKUP Function | ||||||
| Use the VLOOKUP function to lookup the appropriate commission rate based on a sales person's level of sales for a given month. | ||||||
| Then, calculate the total commission paid to each sales person for the month. | ||||||
| Hint: =VLOOKUP(employee sales, table range, the table column number with the commission rate, True) | ||||||
| Commission Rate Lookup Table | ||||||
| Sales | Commission Rate | |||||
| $ 1 | 10% | |||||
| $ 20,000 | 11% | |||||
| $ 40,000 | 12% | |||||
| $ 60,000 | 14% | |||||
| $ 80,000 | 16% | |||||
| $ 100,000 | 18% | |||||
| Employee Sales and Commissions - April | Check Figures | |||||
| Employee Last Name | Division | Sales | Commission Rate (VLOOKUP) | Commission Paid (Sales x Commission Rate) | Commission Rate | Commission Paid |
| Bird | West | $ 79,718 | 14% | $ 11,161 | ||
| Durham | North | $ 17,870 | 10% | $ 1,787 | ||
| Snider | North | $ 46,657 | 12% | $ 5,599 | ||
| Zimmerman | South | $ 34,165 | 11% | $ 3,758 | ||
| Howard | North | $ 75,832 | 14% | $ 10,616 | ||
| Colon | West | $ 39,634 | 11% | $ 4,360 | ||
| Christian | East | $ 20,813 | 11% | $ 2,289 | ||
| Harrell | South | $ 81,104 | 16% | $ 12,977 | ||
| Irwin | East | $ 66,993 | 14% | $ 9,379 | ||
| Woodward | East | $ 17,276 | 10% | $ 1,728 | ||
| Ortega | North | $ 50,333 | 12% | $ 6,040 | ||
| Hoover | South | $ 7,689 | 10% | $ 769 | ||
| Nash | West | $ 60,940 | 14% | $ 8,532 | ||
| Roy | East | $ 12,757 | 10% | $ 1,276 | ||
| Burke | West | $ 101,023 | 18% | $ 18,184 | ||
| Robinson | West | $ 28,460 | 11% | $ 3,131 | ||
| Sharpe | West | $ 71,153 | 14% | $ 9,961 | ||
| Ryan | South | $ 90,808 | 16% | $ 14,529 | ||
| Austin | West | $ 16,684 | 10% | $ 1,668 | ||
| Munoz | South | $ 133,000 | 18% | $ 23,940 | ||
| Petersen | West | $ 79,377 | 14% | $ 11,113 | ||
| Austin | South | $ 67,106 | 14% | $ 9,395 | ||
| Green | West | $ 33,447 | 11% | $ 3,679 | ||
| Goff | West | $ 40,736 | 12% | $ 4,888 | ||
| Elliott | South | $ 78,196 | 14% | $ 10,947 | ||
| Rice | East | $ 54,534 | 12% | $ 6,544 | ||
| Richmond | North | $ 53,723 | 12% | $ 6,447 | ||
| Douglas | South | $ 95,779 | 16% | $ 15,325 | ||
| Mercer | West | $ 109,940 | 18% | $ 19,789 | ||
| Randall | West | $ 22,257 | 11% | $ 2,448 | ||
| Gibson | North | $ 34,210 | 11% | $ 3,763 | ||
| Hickman | East | $ 88,560 | 16% | $ 14,170 | ||
| Morgan | West | $ 84,838 | 16% | $ 13,574 | ||
| Small | South | $ 71,162 | 14% | $ 9,963 | ||
| Dyer | North | $ 56,436 | 12% | $ 6,772 | ||
| Bridges | East | $ 28,947 | 11% | $ 3,184 | ||
| Franklin | West | $ 51,288 | 12% | $ 6,155 | ||
| Total commission paid | $ 299,839 | |||||
IF Function
| IF Function |
| Instructions: Write an IF function that tells the user what they should bring to the beach based on the weather. |
| Parameters |
| If it is sunny, bring your sunglasses |
| If it is snowing, bring your jacket |
| If it is raining, bring your umbrella |
| Enter the weather in cell E10 |
| Enter your IF formula in cell E11 |
| Briefly explain how the IF function works? |
| Describe a scenario where a manager could use an IF function in a spreadsheet that calcuates weekly employee pay. |
Read Me When Finished
| CMR 181 Exam |
| When Done: |
| 1. Make sure you saved the file with the following name. |
| UniqueID 181 Online Excel Exam.xlsx. Use your own UniqueID, for example, mine is maysta |
| 2. Sumbit the exam |
