3.5: 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.5: 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.

Search

Text Color

Text Size

Margin Size

Font Type