Question

CIS 120 Final Project Case Study Grades Analysis 1. 4. Overview Please use the Excel data file to analyze the grades for students in their class. You are required to use either a formula or function to receive points for the steps. Typing in the answer is worth 0 (zero) points. Steps to complete are as following: Separate the student first name and last name into two columns. (5 points) 2. Get the Average, Minimum, and Maximum of Test 1. (5 Points) 3. Get the Average, Minimum, and Maximum of Test 2. (5 points) Get the Average, Minimum, and Maximum of Final Test. (5 points) Calculate the Final Average based on Test 1. Test 2, and Final Test. (5 points) 6. If the first 5 students expect to get the Final Average of 95, what would their Final Tests need to be. (10 points) 7. Assign letter grade to each student after above calculations are completed using Vlookup or Hlookup. (10 points) 8. What is the final grade distributions? In other words, how many students earned A, B, C, D, and F? Use COUNTIF or Pivot Table to organize your data by grade in your excel sheet to show your result. (15 points) Grading scale: 5. >= 0 and <60 >= 60 and <70 >= 70 and <80 >= 80 and <90 >= 90 and <100 с B A their letter grade. (5 points)

Answer 1

1. Step 1: Insert a new column next to the column with full name.

Step 2: Select the names in column.

Step 3: Go to Data -> Text to Columns option (shown in screenshot)

Step 4: Keep the file type as ‘Delimiter’ and click ‘Next’ (shown in screenshot)

Step 5: Select the checkbox for ‘Space’ and uncheck for ‘Tab’ and click next

Step 6: Click ‘Finish’ on next step.

Step 7: Names will get separated to next column. Rename the column headers.

File Step 3ne Insert Page Layout Formulas Data Review View Developer A. A 1 Clear Reapply Y Advanced Step 5 Sort Filter From Access From Web Connections Properties Existing Refresh Connections All Go Edit Links Connections for Name From From Other Text Sources Get External Data Text to Remove Data Consolidate WH Columns Duplicates Validation Ana Data Tools Sort & Filter A1 Convert Text to Columns Wizard - Step 2 of 3 ? X This screen lets you set the delimiters your data contains. You can see how your text is affected in the preview below. А D E F G H K L M B с Fi st Name Last Name Treat consecutive delimiters as one Name Any Frank Liza belle Allen Holy Adam Rocj Grayson Singh Delimiters Tab Semicolon E comma Space Other: Text qualifier: {none} Data preview 9 10 Any Frank Liza belle Allen Holy Adam Rocj Grayson Singh Step 4 Convert Text to Columns Wizard - Step 1 of 3 ? X Cancel < Back Next > Finish The Text Wizard has determined that your data is Delimited. If this is correct, choose Next, or choose the data type that best describes your data. Original data type Choose the file type that best describes your data: O Delimited Characters such as commas or tabs separate each field. Fixed widtb - Fields are aligned in columns with spaces between each field. Step 7 Preview of selected data: ci fxx с D 2 Any Frank 3 Liza belle 4 Allen Holy 5 Adam Rocj 6 Grayson Singh А 1 First Name 2 Any 3 Liza 4 Allen 5 Adam 6 Grayson 7 B Last Name Frank belle Holy Rocj Singh Cancel < Back Next > Finish 8 9

2. Step 1: Let’s consider the sample data as given in the screenshot.

Step 2: Select the cell to put Minimum, Maximum and Average values and put the below formula

Test 1 -> Min and put formula =MIN(C2:C6) where C2: C6 is range of the Test 1 marks

Test 1 -> Max and put formula =MAX(C2:C6) where C2: C6 is range of the Test 1 marks

Test 1 -> Average and put formula =AVERAGE(C2:C6) where C2: C6 is range of the Test 1 marks

A B с D E F G Test2 Final Test 70 45 68 60 78 58 1 First Name 2 Any 3 Liza 4 Allen 5 Adam 6 Grayson 7 Last Name Testi Frank belle Holy Rocj Singh 50 34 45 85 89 98 95 98 99 8 9 Min Max Average 10 11 Test1 12 Test 2 13 Final Test D11 А F 1 First Name 2 Any 3 Liza 4 Allen 5 Adam 6 Grayson 7 fx =AVERAGE(C2:06) B с D E Last Name Test1 Test2 Final Test Frank 70 45 68 belle 60 78 58 Holy 50 34 45 Rocj 85 89 98 Singh 95 98 99 8

3. Repeat the step 2 for Test 2

4. Repeat step 2 for Final Test

Final sheet will look as below:

D12 F А 1 First Name 2 Any 3 Liza 4 Allen 5 Adam 6 Grayson 7 fx =AVERAGE(D2:D6) B с D E Last Name Test1 Test2 Final Test Frank 70 45 68 belle 60 78 58 Holy 50 34 45 Rocj 85 89 98 Singh 95 98 99 8 9 Min Max Average 72 95 10 11 Test1 12 Test 2 13 Final Test 50 34 68.8 98 99 45 73.6

5. Final Average is average of three tests with below logic:

Final Average = (Test 1 Average + Test 2 Average + Final Test Average)/3

Let’s use a formula to calculate the above logic:

AVERAGE(Test 1 Average , Test 2 Average , Final Test Average)

Select the cell where to put the final average and put below formula:

=AVERAGE(D11:D13) where D11:D13 are cells with other averages.

D14 F A First Name 2 Any 3 Liza Allen Adam 5 Grayson fx =AVERAGE(D11:013) B с Last Name Testi Test2 Final Test Frank 70 45 68 belle 60 78 58 Holy 50 34 45 Rocj 85 89 98 Singh 95 98 99 7 m 3 0 Min Max Average 72 50 95 34 98 68.8 45 99 1 Test1 2 Test 2 3 Final Test 4 Final 5 6 73.6 71.47

6. Select a new column where want to insert the new expected marks to obtain 95 average and put below formula:

=95*(3)-SUM(C6:D6) where C6 and D6 are the cells holding Test 1 and Test 2 marks.

Consider the points for student Grayson in below sample:

F6 A G B С Last Name Test1 Frank belle Holy Rocj Singh =95*(3)-SUM(C6:06) D E F Test2 Final Test Expected Final Test to get Average 95 70 45 68 60 78 58 1 First Name 2 Any 3 Liza 4. Allen 5 Adam 6 Grayson 7 50 34 45 98 85 89 95 98 29 IN 8 9 10 Min Max 50 34 11 Test1 12 Test 2 13 Final Test 14 Final Average 95 72 98 68.8 99 73.6 71.47 45 15

**As per HomeworkLib gudielines, answering first 4 parts is mandatory, when required parts to be answered are not mentioned. Kindly refer above 6 parts and let me know if there is any clarification needed at any step. As the data was not available, current solution is on sample data in screenshots.Thanks.