Excel · Week 2 of 4 · 5–6 hours
Basic Formulas and Logical Functions
Make Excel do the calculating, and teach it to make simple decisions.
Learning objectives
By the end of this week you will be able to:
- Write formulas using cell references and arithmetic operators.
- Use SUM, AVERAGE, COUNT, MIN and MAX.
- Know when to use relative and absolute ($) references.
- Use IF, AND, OR, COUNTIF and SUMIF to answer conditional questions.
Lessons
Basic Formulas
Every formula starts with an equals sign =. After that you can use numbers, cell addresses and operators: + add, - subtract, * multiply, / divide, ^ power. Excel follows the normal order of operations, so use parentheses to control it: =(B2+C2)*D2.
Use cell references, not typed numbers. =D2*E2 (Units times Price) updates automatically if either cell changes. =12*4.5 never will.
Functions are built-in formulas. The five you will use most:
| Function | What it does | Example |
|---|---|---|
| SUM | Adds a range | =SUM(F2:F100) |
| AVERAGE | Mean of a range | =AVERAGE(F2:F100) |
| COUNT | Counts cells that hold numbers | =COUNT(F2:F100) |
| COUNTA | Counts cells that are not empty | =COUNTA(B2:B100) |
| MIN / MAX | Smallest / largest value | =MAX(F2:F100) |
Relative vs absolute references. When you copy =D2*E2 down a column, Excel shifts it to =D3*E3, =D4*E4. That is a relative reference and is usually what you want. Sometimes one cell must stay fixed, such as a tax rate in H1. Write it as $H$1 (press F4 to add the dollar signs) so it does not move when copied: =F2*$H$1.
Logical Functions
Logical functions let a formula make a decision. The most important is IF:
=IF(test, value_if_true, value_if_false)
Example: label each sale as Large if it is over 500. =IF(F2>500, "Large", "Small"). The test uses comparison operators: =, <> (not equal), >, <, >=, <=. Text in a formula always goes in double quotes.
AND is TRUE only if every condition is true. OR is TRUE if at least one condition is true. You usually put them inside IF:
=IF(AND(B2="North", F2>500), "Priority", "")flags large North sales.=IF(OR(B2="North", B2="South"), "Domestic", "Export")
Conditional counting and summing. Analysts constantly ask "how many?" and "how much?" for one group:
=COUNTIF(B2:B100, "North")counts rows where Region is North.=SUMIF(B2:B100, "North", F2:F100)totals Sales for North only.COUNTIFSandSUMIFSaccept several conditions, for example North and Product A.
Tip: when IF statements start nesting three or four deep, check whether IFS or a small lookup table (next week) would be simpler.
Watch
- VideoExcel functions for beginners (SUM, AVERAGE, COUNT, MAX and MIN)
A second walkthrough of the same five functions.
Practice
Using your Week 1 file:
- Add a Sales column:
=Units*Unit Price. - Below the data, calculate total, average, minimum and maximum Sales and the number of orders.
- Put a 7% commission rate in a single cell and add a Commission column using an absolute reference.
- Add a Size column with IF: Large if Sales is 500 or more, otherwise Small.
- Build a small summary: total Sales and number of orders for each Region using SUMIF and COUNTIF.
Check your understanding
Try each question first, then open it to see the answer.
What does =COUNT(A1:A10) return if A1:A10 holds 6 numbers and 4 words?
6. COUNT only counts numbers. COUNTA would return 10.
Why write $H$1 instead of H1?
The dollar signs lock the reference so it does not shift when the formula is copied.
Write a formula that returns Yes if B2 is East and C2 is above 100.
=IF(AND(B2="East", C2>100), "Yes", "No")
Assignment
Submit the updated workbook with the Sales, Commission and Size columns and a region summary table. Add one sentence explaining which region sold the most.