The idea
In the earlier modules, every formula did the same calculation on every row. If you wrote =B2*5% and filled it down, Ayu got 5% of her sales, Budi got 5% of his, and so did everyone else. That works for simple maths, but a lot of everyday decisions at work aren't that simple.
Think about how a sales bonus usually works. The company doesn't pay it to everyone, only to the people who actually reached their target that month. So someone has to go down the list, compare each person's sales with their target, and decide yes or no. Or think about a report that shows how much each person grew compared to last month. Someone who joined this month has no "last month" to compare with, and instead of a broken number you'd rather the sheet just said they're new.
Excel's logic functions do that checking for you. You write the rule once, and the formula looks at each row on its own and gives the answer that fits that row.
The examples in this module use a small sheet for a sales team. Each row is one person, with their sales this month, their target, how many years they've worked there, how many days they came in late, and their sales last month:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Staff | Sales | Target | Years | Late days | Last month |
| 2 | Ayu | 4200000 | 4000000 | 3 | 0 | 4000000 |
| 5 | Dewi | 2900000 | 3000000 | 2 | 0 |
Asking yes/no questions
Every decision starts with a question that has a yes or no answer, like "did Ayu reach her target?" In Excel you ask that kind of question with a comparison: you put two values on either side of a sign like > or =, and Excel tells you whether it's true. The answer is always TRUE (yes) or FALSE (no).
| Operator | Means |
|---|---|
= | equal to |
<> | not equal to |
> / < | bigger / smaller than |
>= / <= | bigger / smaller than, or equal |
# Did Ayu reach her target?
H2: =B2>=C2
# Did Budi reach his target?
H3: =B3>=C3
# Is the name in A4 Citra?
H4: =A4="Citra"H2: TRUE
H3: FALSE
H4: TRUEAyu sold 4200000 and her target was 4000000, so the answer to "did she reach it?" is TRUE. Budi sold 3150000 but needed 3500000, so his answer is FALSE. Comparisons work on text too, which is how H4 checks the name. One thing that surprises people: Excel ignores capital letters when it compares text, so ="citra" would also give TRUE.
IF: this or that
TRUE and FALSE are fine for Excel, but if you hand your manager a column full of them, they'll have to stop and work out what each one means. IF lets you turn that yes/no answer into something a person actually wants to see, like a label or an amount of money. You give it three things, in this order:
IF(question, answer if yes, answer if no)
"If you finish your vegetables, you get ice cream. If not, no ice cream." That's an IF. There's a question (did you finish?), a yes-answer (ice cream) and a no-answer (nothing).
H1: Result
H2:H6: =IF(B2>=C2, "Hit", "Missed")H1: Result
H2: Hit
H3: Missed
H4: Hit
H5: Missed
H6: HitExcel checks the question separately for each row, which is why Ayu and Budi end up with different results from the same formula.
The two answers don't have to be words. They can be numbers, or even a calculation that only runs when it's needed. Going back to the bonus example, say the rule is that anyone who reached their target gets 5% of their sales, and anyone who didn't gets nothing:
H1: Bonus
H2:H6: =IF(B2>=C2, B2*5%, 0)H1: Bonus
H2: 210000
H3: 0
H4: 255000
H5: 0
H6: 190000Words go in double quotes, like "Hit". Numbers don't. If you write "0" with quotes, Excel sees text that just looks like zero, and SUM will skip it.
IFS: more than two options
IF only has room for two answers, yes or no. Sometimes you need more than that. Say the team lead wants to give everyone one of three ratings: Star for anyone who sold 5000000 or more, Good for anyone who at least reached their target, and Keep going for everyone else. That's three possible results, and IFS handles it. You give it pairs of question and answer, as many as you need:
IFS(question1, answer1, question2, answer2, ...)
Excel goes through the questions from left to right and stops at the first one that's TRUE, then gives you the answer next to it. It never looks at the rest. Because of that, the order you write them in matters, and two habits will save you trouble:
- Put the hardest one to get first. Citra sold 5100000, which is above her target too. If the "reached target" question came first, Excel would stop there and call her
Good, and it would never get to ask whether she's a Star. - Finish with
TRUE, "something".TRUEon its own is always true, so this last pair catches anyone who didn't match the questions before it. Without it, those people get a#N/Aerror.
H1: Rating
H2:H6: =IFS(B2>=5000000, "Star", B2>=C2, "Good", TRUE, "Keep going")H1: Rating
H2: Good
H3: Keep going
H4: Star
H5: Keep going
H6: GoodIn older spreadsheets you'll often see the same thing written as an IF inside an IF: =IF(B2>=5000000, "Star", IF(B2>=C2, "Good", "Keep going")). It works the same way, it's just harder on the eyes.
AND, OR, XOR, NOT
So far each IF has checked one thing. Real rules often check more than one. Maybe the company decides the bonus is only for people who reached their target and didn't come in late at all this month. That's two questions, but IF only takes one. These four functions solve that by combining several questions into a single TRUE or FALSE, which you can then hand to IF:
| Function | TRUE when |
|---|---|
AND(a, b, ...) | every question is TRUE |
OR(a, b, ...) | at least one is TRUE |
XOR(a, b) | exactly one of the two is TRUE |
NOT(a) | a is FALSE (it flips the answer) |
To get into the movie you need a ticket AND to be old enough. Both, no exceptions. To pay at the shop you can use cash OR a card. Either one is fine.
# Did Ayu reach her target and never come in late?
I2: =AND(B2>=C2, E2=0)
# Has Budi worked 4+ years or reached his target?
I3: =OR(D3>=4, B3>=C3)
# Is exactly one of Citra's two conditions true?
I4: =XOR(B4>=C4, E4=0)
H2:H6: =IF(AND(B2>=C2, E2=0), "Bonus", "-")I2: TRUE
I3: FALSE
I4: TRUE
H2: Bonus
H3: -
H4: -
H5: -
H6: -Look at the last line: only Ayu gets the bonus. Citra and Eko both reached their targets, but each of them came in late at least once, so AND says FALSE for them.
Citra's XOR is TRUE for the same reason. She reached her target (yes) but she wasn't late-free (no), so exactly one of the two questions is true.
In practice you'll use AND and OR all the time, almost always inside an IF like that last line. XOR and NOT come up much less often.
(Strictly speaking, XOR with more than two questions is TRUE when an odd number of them are TRUE. You'll rarely need that.)
IFERROR: a backup answer
To see how much each person grew compared to last month, you divide this month by last month and subtract 1: =B2/F2-1. For Ayu that's 4200000 / 4000000 - 1, which is 0.05, or 5% growth. The problem is Dewi. She only joined this month, so her last month cell (F5) is empty. Excel treats an empty cell as 0 here, and since you can't divide by zero, her row shows #DIV/0!.
That error isn't a mistake in your formula. It's expected, because new people simply don't have a last month. IFERROR(formula, backup) is made for exactly this situation: Excel tries the formula, and if the result is an error, it shows the backup instead.
H2:H6: =IFERROR(B2/F2-1, "New")H2: 0.05
H3: 0.05
H4: 0.02
H5: New
H6: -0.02564102564IFERROR hides every error, including your own typos. If you misspell a function, you'll just see "New" and never know something's wrong. So only use it when you know which error to expect and why. And pick a backup that's honest, like "New" or "n/a". A fake 0 will quietly mess up your averages.
Recap
- Comparisons (
=,<>,<,>,<=,>=) ask yes/no questions. Excel answers TRUE or FALSE. IF(question, if yes, if no)picks between two answers.IFSpicks from several and stops at the first TRUE.ANDneeds all of them,ORneeds just one,XORneeds exactly one,NOTflips the answer.- Words in formulas go in quotes. Numbers don't.
IFERROR(formula, backup)swaps an error you expected for something readable.