Which Excel formula gives pass or fail for each student?

Count failed subjects with COUNTIF and decide with IF: =IF(COUNTIF(C2:E2,"<33")=0,"PASS","FAIL"). Checking only the percentage can pass a student who failed a subject.

How do I give rank in Excel?

Use =RANK.EQ(F2,$F$2:$F$6,0) for highest-first ranking. Equal totals get the same rank.

Do these formulas work in Google Sheets and LibreOffice?

Yes. SUM, AVERAGE, ROUND, IF, COUNTIF, COUNTIFS, RANK.EQ, VLOOKUP, INDEX and MATCH work the same. LibreOffice may show semicolons instead of commas between arguments depending on language settings.

Guide 42 · Student & Teacher IT · Hindi + English

School Result Work के लिए Basic Excel Formulas

Q: How do I give rank in Excel?

Use =RANK.EQ(F2,$F$2:$F$6,0) for highest-first ranking. Equal totals get the same rank.

Q: Do these formulas work in Google Sheets and LibreOffice?

Yes. SUM, AVERAGE, ROUND, IF, COUNTIF, COUNTIFS, RANK.EQ, VLOOKUP, INDEX and MATCH work the same. LibreOffice may show semicolons instead of commas between arguments depending on language settings.

School result sheets में total, percentage, grade और pass/fail status calculate करें.

The result sheet we will build

Marks out of 100 in three subjects; pass mark 33 in each subject. Rule used here (change it to your school’s rule): fail in one subject = COMPARTMENT, fail in two or more = FAIL. Every number below was calculated with the formulas on this page.

	A	B	C	D	E	F	G	H	I
1	Roll	Name	Hindi	English	Maths	Total	%	Result	Rank
2	1	Asha	88	91	95	274	91.33	PASS	1
3	2	Ravi	45	52	29	126	42.00	COMPARTMENT	4
4	3	Neha	76	68	81	225	75.00	PASS	2
5	4	Kabir	30	25	40	95	31.67	FAIL	5
6	5	Isha	70	66	58	194	64.67	PASS	3

Total, percentage and average

Cell	Formula	Meaning
F2	`=SUM(C2:E2)`	Total of three subjects
G2	`=ROUND(F2/300*100, 2)`	Percentage, rounded to 2 decimals
C8	`=AVERAGE(C2:C6)`	Class average in Hindi (61.8)
C9	`=MAX(C2:C6)` / `=MIN(C2:C6)`	Highest / lowest in Hindi (88 / 30)

Type the formula once in row 2, then double-click the small square at the bottom-right of the cell (fill handle) to copy it down for all students.

PASS / COMPARTMENT / FAIL with COUNTIF and IF

First count how many subjects are below 33, then decide:

H2: =IF(COUNTIF(C2:E2,"<33")=0, "PASS", IF(COUNTIF(C2:E2,"<33")=1, "COMPARTMENT", "FAIL"))

Ravi has Maths 29 → one subject below 33 → COMPARTMENT, even though his percentage is 42.
Kabir has Hindi 30 and English 25 → two subjects → FAIL.
Excel 2021/365 users can write it more clearly with IFS or LET.

A common mistake is =IF(G2>=33,"PASS","FAIL") – it passes Ravi on percentage although he failed Maths. Always check each subject.

Rank with RANK.EQ

I2: =RANK.EQ(F2, $F$2:$F$6, 0) – 0 means highest total = rank 1. The $ signs keep the range fixed when copying.

Ties get the same rank (two students with 225 both become 2, the next is 4). Many schools rank only students who passed: =IF(H2="PASS", COUNTIFS($H$2:$H$6,"PASS",$F$2:$F$6,">"&F2)+1, "-").

Grades (A1, A2…) from percentage

Make a grade table with lower limits (0 → E, 33 → D, 41 → C2, 51 → C1, 61 → B2, 71 → B1, 81 → A2, 91 → A1 – adjust to your board) and use an approximate lookup: =VLOOKUP(G2, $K$2:$L$9, 2, TRUE). Asha (91.33) gets A1, Neha (75) gets B1. Details: VLOOKUP and XLOOKUP.

Class summary in four formulas

Question	Formula	Answer
How many passed?	`=COUNTIF(H2:H6,"PASS")`	3
Pass percentage	`=COUNTIF(H2:H6,"PASS")/COUNTA(B2:B6)` (format as %)	60%
Students above 75%	`=COUNTIF(G2:G6,">=75")`	2
Class topper	`=INDEX(B2:B6, MATCH(MAX(F2:F6), F2:F6, 0))`	Asha

Make mistakes visible

Data → Data Validation on C2:E6: Whole number between 0 and 100. Typing 910 instead of 91 is then blocked.
Home → Conditional Formatting → Highlight Cells Rules → Less Than 33 with red fill – failed subjects stand out.
Freeze the header row: View → Freeze Panes → Freeze Top Row.
Protect formula columns: unlock mark cells (Format Cells → Protection), then Review → Protect Sheet.

Cleaning messy marks first? See marksheet data cleaning.

हम कौन सी result sheet बनाएँगे

तीन subjects में 100 में से marks; हर subject में पास अंक 33। यहाँ इस्तेमाल नियम (अपने school के नियम से बदलें): एक subject में fail = COMPARTMENT, दो या ज़्यादा में fail = FAIL। नीचे का हर number इसी page के formulas से निकला है।

	A	B	C	D	E	F	G	H	I
1	Roll	Name	Hindi	English	Maths	Total	%	Result	Rank
2	1	Asha	88	91	95	274	91.33	PASS	1
3	2	Ravi	45	52	29	126	42.00	COMPARTMENT	4
4	3	Neha	76	68	81	225	75.00	PASS	2
5	4	Kabir	30	25	40	95	31.67	FAIL	5
6	5	Isha	70	66	58	194	64.67	PASS	3

Total, percentage और average

Cell	Formula	अर्थ
F2	`=SUM(C2:E2)`	तीन subjects का total
G2	`=ROUND(F2/300*100, 2)`	Percentage, 2 दशमलव तक
C8	`=AVERAGE(C2:C6)`	Hindi में class average (61.8)
C9	`=MAX(C2:C6)` / `=MIN(C2:C6)`	Hindi में सबसे ज़्यादा / कम (88 / 30)

Formula row 2 में एक बार लिखें, फिर cell के नीचे-दाएँ छोटे वर्ग (fill handle) पर double-click करके सभी students के लिए नीचे copy करें।

COUNTIF और IF से PASS / COMPARTMENT / FAIL

पहले गिनें कि कितने subjects 33 से कम हैं, फिर तय करें:

H2: =IF(COUNTIF(C2:E2,"<33")=0, "PASS", IF(COUNTIF(C2:E2,"<33")=1, "COMPARTMENT", "FAIL"))

Ravi का Maths 29 → एक subject 33 से कम → COMPARTMENT, जबकि percentage 42 है।
Kabir का Hindi 30 और English 25 → दो subjects → FAIL।
Excel 2021/365 में IFS या LET से और साफ़ लिख सकते हैं।

आम ग़लती है =IF(G2>=33,"PASS","FAIL") – यह Ravi को percentage पर pass कर देता है जबकि वह Maths में fail है। हमेशा हर subject जाँचें।

RANK.EQ से Rank

I2: =RANK.EQ(F2, $F$2:$F$6, 0) – 0 का मतलब सबसे ज़्यादा total = rank 1। $ चिह्न copy करते समय range को स्थिर रखते हैं।

बराबर अंकों पर एक ही rank (225 वाले दो students दोनों 2, अगला 4)। कई schools सिर्फ़ pass students को rank देते हैं: =IF(H2="PASS", COUNTIFS($H$2:$H$6,"PASS",$F$2:$F$6,">"&F2)+1, "-")।

Percentage से grades (A1, A2…)

निचली सीमाओं वाली grade table बनाएँ (0 → E, 33 → D, 41 → C2, 51 → C1, 61 → B2, 71 → B1, 81 → A2, 91 → A1 – अपने board के हिसाब से बदलें) और approximate lookup लगाएँ: =VLOOKUP(G2, $K$2:$L$9, 2, TRUE)। Asha (91.33) को A1, Neha (75) को B1। विस्तार: VLOOKUP और XLOOKUP।

चार formulas में class summary

सवाल	Formula	जवाब
कितने pass हुए?	`=COUNTIF(H2:H6,"PASS")`	3
Pass percentage	`=COUNTIF(H2:H6,"PASS")/COUNTA(B2:B6)` (% format)	60%
75% से ऊपर students	`=COUNTIF(G2:G6,">=75")`	2
Class topper	`=INDEX(B2:B6, MATCH(MAX(F2:F6), F2:F6, 0))`	Asha

ग़लतियाँ दिखने लायक बनाएँ

C2:E6 पर Data → Data Validation: 0 से 100 के बीच whole number। फिर 91 की जगह 910 type करना रुक जाता है।
Home → Conditional Formatting → Highlight Cells Rules → Less Than 33 लाल रंग के साथ – fail subjects उभरकर दिखते हैं।
Header row freeze करें: View → Freeze Panes → Freeze Top Row।
Formula columns बचाएँ: marks वाली cells unlock करें (Format Cells → Protection), फिर Review → Protect Sheet।

पहले बिखरे marks साफ़ करने हैं? Marksheet data cleaning देखें।

अक्सर पूछे जाने वाले सवाल

हर student का pass या fail कौन सा Excel formula देता है?

COUNTIF से fail subjects गिनें और IF से तय करें: =IF(COUNTIF(C2:E2,"<33")=0,"PASS","FAIL")। सिर्फ़ percentage देखने से subject में fail student भी pass हो सकता है।

Excel में rank कैसे दें?

सबसे ज़्यादा पहले के लिए =RANK.EQ(F2,$F$2:$F$6,0) इस्तेमाल करें। बराबर totals को एक ही rank मिलता है।

क्या ये formulas Google Sheets और LibreOffice में चलते हैं?

हाँ। SUM, AVERAGE, ROUND, IF, COUNTIF, COUNTIFS, RANK.EQ, VLOOKUP, INDEX और MATCH एक जैसे चलते हैं। Language settings के हिसाब से LibreOffice arguments के बीच comma की जगह semicolon दिखा सकता है।