• Practice
  • About 18 min
  • 5 practice questions

Functions in practice: a complete marksheet with SUM, AVERAGE, IF, nested IF, COUNTIF and text functions

Build a marksheet step by step - totals, rounded averages, grades with nested IF, pass/fail, class summaries with COUNTIF - and tidy names with text functions.

If something looks wrong, please tell us.

The best way to master functions is to build something real. A marksheet uses nearly every function asked about in spreadsheet tests, and the steps are the same in Calc and Excel (Calc users may type semicolons between arguments instead of commas).

Task: the data

Names are in A2:A6, and marks in Maths, Science and English in B2:D6. Columns E to H are formulas.

The finished marksheet - every number below was produced by the formulas
NameMathsScienceEnglishTotal (E)Average (F)Grade (G)Result (H)
Meena78856923277.33APass
Arjun45523813545CFail
Farah92889527591.67APass
Kabir60617319464.67BPass
Lata33705816153.67CFail

Step by step

  1. Total. E2: =SUM(B2:D2). Fill down. Meena: 78 + 85 + 69 = 232.
  2. Average. F2: =ROUND(AVERAGE(B2:D2),2). Meena: 232 ÷ 3 = 77.333..., rounded to 77.33. Putting AVERAGE inside ROUND is called nesting.
  3. Grade. G2: =IF(F2>=75,"A",IF(F2>=60,"B",IF(F2>=40,"C","D"))). The tests run in order and stop at the first true one: Kabir's 64.67 fails >=75 but passes >=60, so B.
  4. Result. H2: =IF(MIN(B2:D2)>=40,"Pass","Fail") - a student passes only if the lowest subject mark is at least 40. Arjun's lowest is 38, so Fail, even though his average is 45.
  5. Class summary. =MAX(E2:E6) gives 275 (Farah); =MIN(E2:E6) gives 135; =SUM(B2:B6) gives the Maths total 308.
  6. Counting with a condition. =COUNTIF(H2:H6,"Pass") gives 3. =SUMIF(H2:H6,"Pass",E2:E6) adds the totals of those who passed: 232 + 275 + 194 = 701.

Worked example: checking the nested IF

Lata's average is 161 ÷ 3 = 53.666..., rounded to 53.67. Test 1: 53.67 >= 75? No. Test 2: 53.67 >= 60? No. Test 3: 53.67 >= 40? Yes, so C. Order matters: if the first test were F2>=40, every student from 40 upwards would get the first grade and the later tests would never be reached. Always test the highest band first (or the lowest, consistently).

Conditions inside COUNTIF and SUMIF

A condition with a comparison goes inside quotes, operator first: =COUNTIF(F2:F6,">=60") counts the averages of 60 or more - Meena (77.33), Farah (91.67) and Kabir (64.67), so 3. To take the limit from a cell instead of typing it, join the operator to the cell: =COUNTIF(F2:F6,">="&J1). When you fill such summary formulas, make the ranges absolute (=COUNTIF($F$2:$F$6,">=60")) so they do not slide down the sheet. Finally, =AVERAGE(E2:E6) gives the class's average total: (232 + 135 + 275 + 194 + 161) ÷ 5 = 997 ÷ 5 = 199.4. Re-check every summary like this once by hand; a range that misses one row is the most common silent error.

Text functions on real names

  • =PROPER("meena RAO") → Meena Rao (first letter of each word capital).
  • =TRIM(" CCC exam ") → CCC exam: leading and trailing spaces go, and runs of spaces between words become one. =LEN of that result is 8.
  • =LEFT("priya.sharma",5) → priya; =LEN("priya.sharma") → 12.
  • =MID("SIKHORA",2,3) → IKH: start at character 2 and take 3.
  • =LEFT(A2,1)&". "&B2 builds an initial and surname, such as R. Kumar, from Ravi in A2 and Kumar in B2.

Nest to combine: ROUND(AVERAGE(...),2). Nested IF: test bands in order, highest first. COUNTIF(range,"criterion") counts; SUMIF(range,"criterion",sum_range) adds.

Key takeaways: write each formula once in row 2 and fill down; nest functions to combine steps; order nested IF tests carefully; use MIN inside IF to require every subject; and use COUNTIF and SUMIF for summaries with a condition.

Check your understanding

  1. 1. Using the marksheet, what does =SUM(B4:D4) give for Farah (92, 88, 95)?

    1. 275
    2. 265
    3. 285
    4. 91.67
    Show answer

    Answer: A. 92 + 88 + 95 = 275. 91.67 is her average, not her total.

  2. 2. Meena's marks are 78, 85 and 69. What does =ROUND(AVERAGE(B2:D2),2) show?

    1. 77.3
    2. 77.33
    3. 77.34
    4. 232
    Show answer

    Answer: B. Total = 232, average = 232 ÷ 3 = 77.333... Rounded to 2 decimal places (third decimal is 3, below 5): 77.33.

  3. 3. With G2: =IF(F2>=75,"A",IF(F2>=60,"B",IF(F2>=40,"C","D"))), which grade does an average of 64.67 get?

    1. A
    2. B
    3. C
    4. D
    Show answer

    Answer: B. 64.67 >= 75 is false, so go to the next test: 64.67 >= 60 is true, so the result is B and the remaining test is not checked.

  4. 4. H2:H6 hold Pass, Fail, Pass, Pass, Fail. What does =COUNTIF(H2:H6,"Pass") return?

    1. 2
    2. 5
    3. TRUE
    4. 3
    Show answer

    Answer: D. COUNTIF counts the cells equal to Pass: three of the five cells.

  5. 5. What does =LEN(TRIM(" CCC exam ")) return?

    1. 8
    2. 12
    3. 7
    4. 9
    Show answer

    Answer: A. TRIM removes the leading and trailing spaces and reduces the double space between words to one, leaving 'CCC exam': 3 letters + 1 space + 4 letters = 8.

Privacy · Terms · Refunds · Delivery