3.6: Excel IF Statements

Last updated
Save as PDF

Page ID: 228366

$ \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } $

$ \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} $

$ \newcommand{\dsum}{\displaystyle\sum\limits} $

$ \newcommand{\dint}{\displaystyle\int\limits} $

$ \newcommand{\dlim}{\displaystyle\lim\limits} $

$ \newcommand{\id}{\mathrm{id}}$ $ \newcommand{\Span}{\mathrm{span}}$

( \newcommand{\kernel}{\mathrm{null}\,}\) $ \newcommand{\range}{\mathrm{range}\,}$

$ \newcommand{\RealPart}{\mathrm{Re}}$ $ \newcommand{\ImaginaryPart}{\mathrm{Im}}$

$ \newcommand{\Argument}{\mathrm{Arg}}$ $ \newcommand{\norm}[1]{\| #1 \|}$

$ \newcommand{\inner}[2]{\langle #1, #2 \rangle}$

$ \newcommand{\Span}{\mathrm{span}}$

$ \newcommand{\id}{\mathrm{id}}$

$ \newcommand{\Span}{\mathrm{span}}$

$ \newcommand{\kernel}{\mathrm{null}\,}$

$ \newcommand{\range}{\mathrm{range}\,}$

$ \newcommand{\RealPart}{\mathrm{Re}}$

$ \newcommand{\ImaginaryPart}{\mathrm{Im}}$

$ \newcommand{\Argument}{\mathrm{Arg}}$

$ \newcommand{\norm}[1]{\| #1 \|}$

$ \newcommand{\inner}[2]{\langle #1, #2 \rangle}$

$ \newcommand{\Span}{\mathrm{span}}$ $ \newcommand{\AA}{\unicode[.8,0]{x212B}}$

$ \newcommand{\vectorA}[1]{\vec{#1}} % arrow$

$ \newcommand{\vectorAt}[1]{\vec{\text{#1}}} % arrow$

$ \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } $

$ \newcommand{\vectorC}[1]{\textbf{#1}} $

$ \newcommand{\vectorD}[1]{\overrightarrow{#1}} $

$ \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} $

$ \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} $

$ \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } $

$\newcommand{\longvect}{\overrightarrow}$

$ \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} $

$\newcommand{\avec}{\mathbf a}$ $\newcommand{\bvec}{\mathbf b}$ $\newcommand{\cvec}{\mathbf c}$ $\newcommand{\dvec}{\mathbf d}$ $\newcommand{\dtil}{\widetilde{\mathbf d}}$ $\newcommand{\evec}{\mathbf e}$ $\newcommand{\fvec}{\mathbf f}$ $\newcommand{\nvec}{\mathbf n}$ $\newcommand{\pvec}{\mathbf p}$ $\newcommand{\qvec}{\mathbf q}$ $\newcommand{\svec}{\mathbf s}$ $\newcommand{\tvec}{\mathbf t}$ $\newcommand{\uvec}{\mathbf u}$ $\newcommand{\vvec}{\mathbf v}$ $\newcommand{\wvec}{\mathbf w}$ $\newcommand{\xvec}{\mathbf x}$ $\newcommand{\yvec}{\mathbf y}$ $\newcommand{\zvec}{\mathbf z}$ $\newcommand{\rvec}{\mathbf r}$ $\newcommand{\mvec}{\mathbf m}$ $\newcommand{\zerovec}{\mathbf 0}$ $\newcommand{\onevec}{\mathbf 1}$ $\newcommand{\real}{\mathbb R}$ $\newcommand{\twovec}[2]{\left[\begin{array}{r}#1 \\ #2 \end{array}\right]}$ $\newcommand{\ctwovec}[2]{\left[\begin{array}{c}#1 \\ #2 \end{array}\right]}$ $\newcommand{\threevec}[3]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \end{array}\right]}$ $\newcommand{\cthreevec}[3]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \end{array}\right]}$ $\newcommand{\fourvec}[4]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}$ $\newcommand{\cfourvec}[4]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}$ $\newcommand{\fivevec}[5]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}$ $\newcommand{\cfivevec}[5]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}$ $\newcommand{\mattwo}[4]{\left[\begin{array}{rr}#1 \amp #2 \\ #3 \amp #4 \\ \end{array}\right]}$ $\newcommand{\laspan}[1]{\text{Span}\{#1\}}$ $\newcommand{\bcal}{\cal B}$ $\newcommand{\ccal}{\cal C}$ $\newcommand{\scal}{\cal S}$ $\newcommand{\wcal}{\cal W}$ $\newcommand{\ecal}{\cal E}$ $\newcommand{\coords}[2]{\left\{#1\right\}_{#2}}$ $\newcommand{\gray}[1]{\color{gray}{#1}}$ $\newcommand{\lgray}[1]{\color{lightgray}{#1}}$ $\newcommand{\rank}{\operatorname{rank}}$ $\newcommand{\row}{\text{Row}}$ $\newcommand{\col}{\text{Col}}$ $\renewcommand{\row}{\text{Row}}$ $\newcommand{\nul}{\text{Nul}}$ $\newcommand{\var}{\text{Var}}$ $\newcommand{\corr}{\text{corr}}$ $\newcommand{\len}[1]{\left|#1\right|}$ $\newcommand{\bbar}{\overline{\bvec}}$ $\newcommand{\bhat}{\widehat{\bvec}}$ $\newcommand{\bperp}{\bvec^\perp}$ $\newcommand{\xhat}{\widehat{\xvec}}$ $\newcommand{\vhat}{\widehat{\vvec}}$ $\newcommand{\uhat}{\widehat{\uvec}}$ $\newcommand{\what}{\widehat{\wvec}}$ $\newcommand{\Sighat}{\widehat{\Sigma}}$ $\newcommand{\lt}{<}$ $\newcommand{\gt}{>}$ $\newcommand{\amp}{&}$ $\definecolor{fillinmathshade}{gray}{0.9}$

3.6: Conditional Statements and Excel IF Functions

Beyond searching, Boolean logic is commonly used in spreadsheet applications like Excel to do conditional calculations. A statement is something that is either true or false. A statement like 3 < 5 is true; a statement like "x < 5" is true for some values of x and false for others. When an action is taken or not depending on the value of a statement, it forms a conditional.

Statements and Conditionals
A statement is either true or false.
A conditional is a compound statement of the form "if p then q" or "if p then q, else s."

Example 25

In common language, an example of a conditional statement would be: "If it is raining, then we'll go to the mall. Otherwise we'll go for a hike."

The statement "If it is raining" is the condition — this may be true or false for any given day. If the condition is true, we go to the mall. If the condition is false, we use the alternative, and go for a hike.

Example 26 — IF in Excel

Conditional statements are commonly used in spreadsheet applications like Excel or Google Sheets. In Excel, you can enter an expression like:

=IF(A1<2000, A1+1, A1*2)

After the IF, there are three parts: the condition, and then two calculations. Excel will look at the value in cell A1 and compare it to 2000.

If the condition is true, the first calculation is used: add 1 to A1.
If the condition is false, the second calculation is used: multiply A1 by 2.

In other words: "If the value of A1 is less than 2000, then add 1 to it. Otherwise, multiply it by 2."

Example 27

The expression =IF(A1>5, 2*A1, 3*A1) is used. Find the result if A1 is 3, and the result if A1 is 8.

This is equivalent to: "If A1 > 5, then calculate 2*A1. Otherwise, calculate 3*A1."

If A1 is 3: the condition 3 > 5 is false, so calculate 3*3 = 9
If A1 is 8: the condition 8 > 5 is true, so calculate 2*8 = 16

Example 28

An accountant needs to withhold 15% of income for taxes if the income is below $30,000, and 20% of income if the income is $30,000 or more. Write an Excel expression that would calculate the amount to withhold (assume income is in cell A1).

=IF(A1<30000, 0.15*A1, 0.20*A1)

Combining Conditions with AND and OR

We can create more complex conditions by using AND and OR to join simpler conditions together.

Example 29

A parent might say to their child "if you clean your room and take out the garbage, then you can have ice cream." Both conditions must be true for the child to get ice cream.

If the parent had said "if you clean your room or take out the garbage, then you can have ice cream," then the child would need to complete only one chore.

In Excel syntax:

For "A1 < 300 and A1 > 100": AND(A1<300, A1>100)
For "A1 = 4 or A1 = 6": OR(A1=4, A1=6)

Example 30

In a spreadsheet, cell A1 contains annual income and A2 contains number of dependents. A certain tax credit applies if someone with no dependents earns less than $10,000, or if someone with dependents earns less than $20,000. Write an Excel rule for this.

The two ways the rule is met:

income < 10,000 and dependents = 0, or
income < 20,000 and dependents > 0

=IF(OR(AND(A1<10000, A2=0), AND(A1<20000, A2>0)), "you qualify", "you don't qualify")

Try it Now

A store gives a 10% discount if a customer is a member, or if their purchase total exceeds $100. Write an Excel IF statement for this scenario (assume membership status is in A1 as TRUE/FALSE, and purchase total is in B1).

This section is remixed from Quantitative Reasoning (Lachniet et al., 2026), §4.7, licensed CC-BY-SA.

Nested IF Statements

A nested IF places a second IF statement inside the first one. This lets you handle three or more outcomes instead of just two.

Rule: Nested IF Structure

A basic IF handles two outcomes (true or false). When you need three or more outcomes, place another IF in the "false" position:

=IF(condition1, result1, IF(condition2, result2, result3))

Excel checks condition1 first. If true, it returns result1 and stops. If false, it moves to the inner IF and checks condition2. If that is true, it returns result2. If both are false, it returns result3.

How Excel Reads a Nested IF — Step by Step

Step	Excel Asks	If True	If False
1	Is condition1 true?	Return result1 — done	Move to step 2
2	Is condition2 true?	Return result2 — done	Move to step 3
3	Neither condition was true	Return result3 — done

Example 31 — Letter Grade from a Score

A student's score is in cell A1. Assign a letter grade: A if score ≥ 90, B if score ≥ 80, otherwise C.

Always Check the Highest Threshold First

Test conditions from highest to lowest (or lowest to highest consistently). If you check ≥ 80 before ≥ 90, a score of 95 would be caught by the first condition and returned as B — wrong. Check ≥ 90 first.

Step	Formula / Result
Check highest threshold first: is A1 ≥ 90?	IF(A1>=90, "A", ...)
If not, check next: is A1 ≥ 80?	IF(A1>=80, "B", ...)
If neither, return C	"C"
Complete formula	=IF(A1>=90,"A",IF(A1>=80,"B","C"))

Verify with test values:

Score (A1)	Excel's Logic	Result
95	95 ≥ 90 is TRUE → return "A", stop	A ✓
83	83 ≥ 90 is FALSE → check inner IF: 83 ≥ 80 is TRUE → return "B"	B ✓
71	71 ≥ 90 is FALSE → 71 ≥ 80 is FALSE → return "C"	C ✓

Example 32 — Density Category (Four Outcomes)

A density value is in cell A1. Classify it as: "Very Low" if < 1.0, "Low" if < 2.0, "Medium" if < 3.0, otherwise "High".

This requires three nested IFs to produce four possible outcomes.

Step	Formula Being Built
Check lowest threshold: is A1 < 1.0?	IF(A1<1, "Very Low", ...)
If not, check next: is A1 < 2.0?	IF(A1<2, "Low", ...)
If not, check next: is A1 < 3.0?	IF(A1<3, "Medium", "High")
Complete formula	=IF(A1<1,"Very Low",IF(A1<2,"Low",IF(A1<3,"Medium","High")))

Verify with test values:

Density (A1)	Excel's Logic	Result
0.7	0.7 < 1 is TRUE → "Very Low", stop	Very Low ✓
1.4	1.4 < 1 FALSE → 1.4 < 2 TRUE → "Low", stop	Low ✓
2.6	2.6 < 1 FALSE → 2.6 < 2 FALSE → 2.6 < 3 TRUE → "Medium"	Medium ✓
4.1	All three conditions FALSE → "High"	High ✓

Counting Closing Parentheses

Each IF needs its own closing parenthesis. With three nested IFs you need three closing parentheses at the end: ...,"High"))). A common error is forgetting one. Count your opening IFs — that is how many closing parentheses you need at the end.

Summary — Nested IF Statements

A nested IF places a second (or third) IF inside the false position of the outer IF
Each additional IF adds one more possible outcome
Always test conditions in order from lowest to highest threshold (or highest to lowest — be consistent)
The number of closing parentheses at the end equals the number of IF functions used
Always verify your formula with test values that hit each possible outcome

Search

Text Color

Text Size

Margin Size

Font Type