Q. Reproduce the following data in MS-Excel sheet and perform the calculation using formula in MS-Excel Sheet.
Total Marks Percentage First Last
No. Full Name GUJARATI HINDI MATHS SCIENCE ENGLISH Result Class Count<25 Status
(out of 350) (%) Name Name
1 Rahul Panara 68 55 62 48 50
2 Sanjay Prajapat 65 59 55 44 35
3 Amrita Singh 63 50 41 39 36
4 Vikas Iyer 41 40 47 42 37
5 Manoj Pandey 40 38 35 24 64
6 Bhavesh Kundal 61 53 46 37 44
7 Divya Gandhi 69 56 52 15 22
8 Ramesh Kumar 66 57 59 48 67
9 Asmita Gupta 37 39 43 41 51
10 Mahendra Sharma 37 35 30 31 38
All cells are Case Sensitive & marks will be deducted in case of Spelling Mistakes
1. Heading - Font Style - Arial, Font Size- 12, Background Standard Color - Purple
2. Calculate Total Marks: Total Marks = Gujarati + Hindi + Maths + Science + English {Value should be in 2 decimals}
3. Calculate Percentage: Percentage = (Total Marks/350) X 100 {value should be in 2 decimals}
4. Calculate "RESULT" using IF condition only:
If Marks in any subject (i.e., GUJARATI, HINDI, MATHS, SCIENCE, and ENGLISH) is less than 25 then consider "FAIL" else "PASS"
5. Calculate "CLASS" using IF condition only:
If "Percentage" is greater than & equal to 70 then consider "DISTINCTION"
If "Percentage" is less than 70 & greater than equal to 60 then consider "FIRST"
If "Percentage" is less than 60 & greater than equal to 50 then consider "SECOND"
If "Percentage" is less than 50 & greater than equal to 35 then consider "THIRD"
If Marks in any subject (i.e., GUJARATI, HINDI, MATHS, SCIENCE, and ENGLISH) is less than 25 then consider "FAIL"
6. Calculate Count in (COUNT<25) COLUMN for no. of subjects in which each student has scored less than 25 marks (Using COUNTIF function Only)
7. Calculate "STATUS" using IF condition only:
If value in "COUNT<25" Column is equal to 0 then consider "Pass"
If value in "COUNT<25" Column is equal to 1 then consider "Atkt" else "Fail"
8. Separate First name and Last name in their respective column
9. Shade the following cell address:
i. Cell H2 to H11 (Brown, Accent 6, Lighter 40%)
ii. Cell 12 to 111 (Background Standard Color Orange)
iii. Cell 12 to 16 (Background Standard Color Blue)
iv. Cell J2 to J11 (Brown, Accent 3, Darker 25%)
10. Create 2D Pie-Chart for "Full Name" & "Percentage (%)" columns
11. All cells should be Center Aligned.
