Nested IF and the IFS Function

A single IF gives two outcomes. Governor reports need Low / Medium / High bands per department — three or more branches.

Dr. Helen Marsh asked for colour-coded budget bands: under £45k, £45k–£55k, over £55k. Nested IF worked; IFS made the formula readable in the committee pack.

On Budgets, annual totals in column F drive the band labels you will build in column M.

You already used =IF(F2>50000,"Review","OK") in column K.

Three parts: test, if true, if false.

Nested IF adds another IF as the false (or true) branch:

=IF(F2>55000,"High",IF(F2>=45000,"Medium","Low"))

In M1, type Budget band. In M2, type =IF(F2>55000,"High",IF(F2>=45000,"Medium","Low")). Press Enter. Copy M2 down to M6.

Science (total 72700) should show High. Arts (lower total) should show Low.

Mathematics annual total is 60500. With thresholds 45000 and 55000, the nested IF returns High — triggering a different slide in the governor deck.

The IFS function tests conditions in order and returns the first match:

=IFS(F2>55000,"High",F2>=45000,"Medium",F2<45000,"Low")

Each test sits on its own line in the formula bar — easier to read than IF(IF(IF(...))).

Requires Excel 2019+ / M365. Older Excel may need nested IF.

If no condition is true, IFS returns #N/A! — add a final catch-all if needed.

Put most specific tests first: >55000 before >=45000. If you write =IFS(F2>=45000,"Medium",F2>55000,"High"), every value above 45000 matches Medium first — High never runs.

On Expenses, flag large Science equipment orders:

=IF(AND(B2="Science",E2>500),"Check","OK")

AND requires every test to be true. OR needs any one true.

Add a Flag column on Expenses. Enter the IF/AND formula on row 2. Fill down. The £610 textbook row for Science should flag Check.

For many bands (ten grant tiers), a lookup table on a Rates sheet with XLOOKUP or VLOOKUP scales better than long IFS lists. The next lesson covers lookups.