📊 We use cookies & analytics to improve your experience. Learn more
Latest

Excel for Freshers — From Spreadsheet to Shortlist Module 2 · Lesson 5 of 15

0% Exit to course

Module 2 · The formulas you will actually use Lesson 5 of 15

IF and nested IF without the panic

Learning objective

IF asks a yes/no question and returns one of two answers. Nesting is just putting another IF in the else position, and it stops being frightening once you write each branch on its own line.

The concept

IF(condition, value_if_true, value_if_false) picks between two results. Nested inside each other, IFs grade, band and label: pass or fail, then first class or distinction. Read them from the inside out, like brackets, and they stop looking like a puzzle.

Past three levels, switch to IFS(condition1, result1, condition2, result2, ...) where only the first true condition fires — no nesting at all. For a set of bands that will be maintained by somebody else, XLOOKUP with an approximate match against a small table is even better, because the bands live in cells rather than inside the formula.

Spreadsheet
=IF(A2 >= 40, "Pass", "Fail")

=IF(A2 >= 75, "Distinction",
   IF(A2 >= 40, "Pass", "Fail"))        read it from the inside out

=IFS(A2 >= 75, "Distinction",
     A2 >= 40, "Pass",
     TRUE,     "Fail")                  first TRUE wins

=XLOOKUP(A2, bands_min, bands_label, , -1)   -- approximate: bands in a table

Common mistakes

The mistake is a condition in the wrong order — a grade of 40 tested before one of 75 makes the higher band unreachable. Test the most restrictive condition first, and print the result on a couple of rows by hand to check before filling the column down.

Worked example

=IF(A2>=75,"Distinction",IF(A2>=60,"First",IF(A2>=40,"Pass","Fail"))) reads top-down, and the order of the tests is what makes it correct — the strictest condition has to come first.

Your practice task

Write a grade formula for one column, then add a second column that returns a comment like "needs revision" using IF and AND together.

Do this before moving on. Reading a solution teaches the shape of it; typing it teaches you.

Key takeaway

IF(condition, value_if_true, value_if_false) picks between two results.

Create free account or sign in

Almost there

15 lessons left of 15

Finish every lesson to unlock the certificate. Each one ends in a practice task — do them and the final project brief writes itself.

Next recommended course Spoken English & Interview Communication