Scribd
Upload
2 views4 pages

Excel Logic Building

The document provides a set of Excel practice exercises focused on building logic and formulas for analyzing school data. It includes basic, intermediate, and advanced questions that cover calculations such as total marks, percentages, attendance, and performance evaluations. Each question is accompanied by the necessary formula or method to achieve the desired results using Excel functions.

Uploaded by

Syed Arsalan
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views4 pages

Excel Logic Building

The document provides a set of Excel practice exercises focused on building logic and formulas for analyzing school data. It includes basic, intermediate, and advanced questions that cover calculations such as total marks, percentages, attendance, and performance evaluations. Each question is accompanied by the necessary formula or method to achieve the desired results using Excel functions.

Uploaded by

Syed Arsalan
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

EXCEL LOGIC BUILDING

PRACTICE EXERCISE
Use the sheet: Raw_School_Data

SECTION A: BASIC QUESTIONS


Q1. Calculate Total Marks for each student
Formula:
=SUM(F2:K2)

Q2. Calculate Percentage for each student


Formula:
=AVERAGE(F2:K2)

Q3. If Percentage is 40 or more, Result = Pass,


otherwise Result = Fail
Formula:
=IF(M2>=40,"Pass","Fail")

Q4. Show only Class 1 students


Answer:
• Use Filter on Class column (D)
• Select 1

Q5. How many students are Fail?


Formula:
=COUNTIF(N:N,"Fail")

Q6. How many students are Pass?


Formula:
=COUNTIF(N:N,"Pass")

Q7. How many Male students are there?


Formula:
=COUNTIF(C:C,"Male")

Q8. How many Female students are there?


Formula:
=COUNTIF(C:C,"Female")
SECTION B: INTERMEDIATE QUESTIONS
Q9. Give Grade based on Percentage:
• 85 or more → A+
• 70 or more → A
• 55 or more → B
• 40 or more → C
• Below 40 → D
(Grade column = O)
Formula:
=IF(M2>=85,"A+",
IF(M2>=70,"A",
IF(M2>=55,"B",
IF(M2>=40,"C","D"))))

Q10. Calculate Fees Pending


(Fees Total – Fees Submitted)
Formula:
=P2-Q2

Q11. How many students got Grade A+?


Formula:
=COUNTIF(O:O,"A+")

Q12. How many students have Fees Pending more than 0?


Formula:
=COUNTIF(R:R,">0")

Q13. Show students whose Attendance is less than 75


Answer:
• Use Filter on Attendance column (S)
• Select Less than 75

Q14. How many students have Attendance 90 or more?


Formula:
=COUNTIF(S:S,">=90")

Q15. Find average percentage of Female students


Formula:
=AVERAGEIF(C:C,"Female",M:M)
Q16. Find average attendance of Male students
Formula:
=AVERAGEIF(C:C,"Male",S:S)

Q17. Find average percentage of Class 3 students


Formula:
=AVERAGEIF(D:D,3,M:M)

Q18. Show Class 10 students who are Fail


Answer:
• Filter Class = 10
• Filter Result = Fail

SECTION C: LOGIC + CONDITIONS


Q19. Set Performance using rules:
• Attendance 90 or more and Percentage 75 or more → Excellent
• Attendance 75 or more → Good
• Else → Average
(Performance column = T)
Formula:
=IF(AND(S2>=90,M2>=75),"Excellent",
IF(S2>=75,"Good","Average"))

Q20. How many students have Excellent Performance?


Formula:
=COUNTIF(T:T,"Excellent")

Q21. Which House Color has the most students?


Answer:
• Count values in House Color column (U)
• Compare numbers

Q22. How many students:


• Passed
• But attendance is less than 75?
Answer:
• Filter Result = Pass
• Filter Attendance < 75

Q23. Find the highest percentage in each class


Answer:
• Filter by class
• Sort Percentage from highest to lowest

Q24. Which class has the highest total Fees Pending?


Formula (check each class):
=SUMIF(D:D,class,R:R)

Q25. FINAL QUESTION


Find:
• Total students
• Pass percentage
• Fail percentage
• Total Fees Pending
Formulas:
Total students:
=COUNTA(A:A)

Pass percentage:
=COUNTIF(N:N,"Pass")/COUNTA(A:A)*100

Fail percentage:
=COUNTIF(N:N,"Fail")/COUNTA(A:A)*100

Total Fees Pending:


=SUM(R:R)

You might also like