📊 Lesson 2.2: Everyday Functions — SUM, AVERAGE, COUNT, IF, ROUND & TODAY

What a Function Is (& AutoSum)

A function is a built-in, named calculation that Excel already knows how to do. You've been using operators like + and * to build formulas by hand; a function is a pre-packaged recipe you call by name. Every function follows the same shape: a name, then a pair of parentheses, and inside them the arguments — the inputs the function needs to do its job.

=FUNCTIONNAME(argument1, argument2, …)

Take the most common one: =SUM(B2:B7). The name is SUM, the single argument is the range B2:B7, and the function returns the total. Compare that to writing =B2+B3+B4+B5+B6+B7 by hand — same answer, but the function is shorter, less error-prone, and keeps working even if you insert rows. Some functions take one argument, some take several separated by commas, and a few (like TODAY()) take none at all — but the parentheses are always there, even when empty.

You don't have to type functions from scratch. The fastest way to total a column is AutoSum: click the empty cell just below a column of numbers, then click the AutoSum button (the Σ symbol, on the Home tab and the Formulas tab) or press its shortcut Alt+=. Excel guesses the range you want, writes =SUM(...) for you, and you just press Enter to accept. The little dropdown arrow next to AutoSum offers Average, Count, Max, and Min too — the same one-click convenience for the other everyday functions. AutoSum is where most people meet their very first function, and it's a great habit.

SUM, AVERAGE, MIN & MAX

These four are the bread and butter of summarizing numbers, and they all work the same way: give them a range and they return one number about it. If you learn nothing else this lesson, learn these.

• SUM adds everything in the range. =SUM(B2:B7) totals seven cells. It ignores blank cells and text, so a stray label won't break it.
• AVERAGE gives the arithmetic mean — the sum divided by how many numbers there are. =AVERAGE(B2:B7). It ignores blanks (which matters — an empty cell is not counted as a zero).
• MIN returns the smallest number in the range: =MIN(B2:B7).
• MAX returns the largest: =MAX(B2:B7).

You can also sum non-adjacent cells by listing them: =SUM(B2, B5, B9) adds just those three. And you can mix ranges and cells: =SUM(B2:B7, B10). The comma is how you say "and also." This flexibility is why SUM shows up everywhere — it happily takes whatever combination of cells and ranges you throw at it.

COUNT vs COUNTA

Counting sounds trivial until you realize there are two kinds, and picking the wrong one gives wrong answers. The distinction is simple once you see it:

• COUNT counts only cells that contain numbers. =COUNT(B2:B20) tells you how many numeric entries are in that range. Text and blanks are ignored.
• COUNTA counts every cell that is not empty — numbers, text, dates, anything. =COUNTA(A2:A20) tells you how many cells have something in them.

So if column A holds item names (text) and column B holds amounts (numbers), you'd use COUNTA(A2:A20) to count how many items you have, and COUNT(B2:B20) to count how many have an amount filled in. Use COUNT for "how many numbers," COUNTA for "how many entries." A memory hook: the extra A in COUNTA stands for "All (non-empty) — including text."

ROUND (and ROUNDUP / ROUNDDOWN)

Calculations often produce more decimal places than you want — =100/3 gives 33.33333… forever. It's important to know the difference between displaying fewer decimals and actually rounding the value. Number formatting (from Lesson 1.3) changes only what you see; the cell still stores the full precise number. ROUND genuinely changes the value, which matters when other formulas build on it (for example, to avoid tiny rounding pennies in a financial total).

ROUND takes two arguments: the number and how many decimal places to keep. =ROUND(33.3333, 2) gives 33.33. =ROUND(A1, 0) rounds to a whole number. You can even round to the left of the decimal with a negative digit count: =ROUND(1234, -2) gives 1200 (to the nearest hundred).

Use plain ROUND most of the time. Reach for ROUNDUP when you can't have a partial unit (you need 3 boxes even if 2.1 would technically do) and ROUNDDOWN when you must never overstate (how many whole items fit in a budget). They share the exact same two-argument shape.

IF — Making a Cell Decide

This is the function that makes spreadsheets feel intelligent. IF lets a cell make a decision: it checks a condition, and returns one thing if the condition is true and a different thing if it's false. It takes exactly three arguments, in this order:

=IF(logical test, value if true, value if false)

The logical test is any question that comes out true or false, usually built with a comparison operator: = (equal), > (greater than), < (less than), >=, <=, and <> (not equal). The other two arguments are what to put in the cell for each outcome. Text results must go in quotation marks; numbers and formulas don't.

A worked example. Suppose column C holds a spending amount and you want a status: "Over budget" when it's more than 100, otherwise "OK." In D2 you'd write:

=IF(C2>100, "Over budget", "OK")

Read it in plain English: "IF C2 is greater than 100, put the text Over budget, otherwise put OK." Fill that down the column (the C2 is relative, so it becomes C3, C4…) and every row labels itself. The value-if-true and value-if-false can also be numbers or even formulas — for example =IF(B2>0, B2*0.1, 0) gives a 10% figure only when B2 is positive, and 0 otherwise. That's IF guarding against a bad calculation.

TODAY() and NOW() — Live Dates

Two special functions put the current date and time into a cell — and keep them current. =TODAY() returns today's date; =NOW() returns the current date and time. Both take no arguments, but you still write the empty parentheses. They're perfect for a "last updated" stamp, a report header, or calculating how many days until something is due.

The important word is volatile. These functions recalculate automatically — every time the workbook recalculates (which happens whenever you edit almost anything, and again each time you open the file). So =TODAY() always shows today, not the day you typed it. That's exactly what you want for a live "today" stamp. But it also means it's not a permanent record: if you need to freeze the date you did something (a fixed transaction date, say), type the date instead of using TODAY(), or use the keyboard shortcut for a static date — Ctrl+; (semicolon) inserts today's date as a fixed value that never changes, and Ctrl+Shift+; inserts the current time.

Because Excel stores dates as numbers under the hood (much more on that next lesson), you can do arithmetic with TODAY(). =A2-TODAY() where A2 is a due date tells you how many days remain; a negative answer means it's overdue. That single trick — subtracting TODAY() from a deadline — powers countless trackers and dashboards.

Function Reference & Reading ScreenTips

Here's a one-stop reference for every function in this lesson. Bookmark this table — it covers the calculations you'll reach for most often.

Let Excel help you type — ScreenTips & autocomplete

You don't have to memorize every function's arguments. As soon as you type = and a few letters, Excel shows an autocomplete list of matching function names; use the arrow keys and press Tab to insert the highlighted one. The moment you open the parenthesis, a small ScreenTip appears showing the function's expected arguments, with the one you're currently entering shown in bold. So =IF( pops up IF(logical_test, [value_if_true], [value_if_false]) and bolds each part as you go. Square brackets in a ScreenTip mean an argument is optional. Reading the ScreenTip as you type is the single best habit for learning new functions on your own — it's a live cheat sheet.

🎯 Project: A Smarter Budget Sheet

Let's turn a plain list of expenses into a sheet that summarizes and thinks for itself. You'll add a total, an average, a count, an IF-driven status column, and a live TODAY() stamp — every function from this lesson, working together on real data. This is the "Formulas" stage of the course made concrete, and it's the foundation of the budget tracker you'll expand in Module 7.

     A            B                    C          D          E
1    Category     Amount               Status     Updated:   =TODAY()
2    Groceries    85                   =IF(B2>100,"Over budget","OK")
3    Rent         1200                 =IF(B3>100,"Over budget","OK")
4    Utilities    140                  =IF(B4>100,"Over budget","OK")
5    Phone        45                   =IF(B5>100,"Over budget","OK")
6    Gym          30                   =IF(B6>100,"Over budget","OK")
7    Dining       160                  =IF(B7>100,"Over budget","OK")
8    Transport    75                   =IF(B8>100,"Over budget","OK")
9    TOTAL        =SUM(B2:B8)
10
11   Average      =ROUND(AVERAGE(B2:B8),2)
12   # of items   =COUNT(B2:B8)
13   Biggest      =MAX(B2:B8)

Tip: type C2's IF once, then fill down — the B2 becomes B3, B4...
because it's a relative reference (from Lesson 2.1).

Best Practices for Functions

✅ Do's

• Prefer a function over hand-typed arithmetic for anything involving a range. =SUM(B2:B100) keeps working when you add rows; =B2+B3+… doesn't.
• Read the ScreenTip as you type a new function — the bolded argument tells you exactly what Excel wants next.
• Use the status bar to gut-check a sum or average before trusting a formula.
• Put text results of IF in quotes and keep numbers unquoted.

❌ Don'ts

• Don't confuse formatting with rounding. Formatting hides decimals for display; ROUND changes the stored value. Use ROUND when later formulas depend on the rounded number.
• Don't assume AVERAGE treats blanks as zero — it ignores them, which can surprise you. Decide whether an empty cell should count.
• Don't use TODAY() for a date you need frozen — it keeps changing. Type it or press Ctrl+;.
• Don't forget the parentheses, even for no-argument functions like =TODAY().

📓 Learning Journal

📝 Lesson Summary

🎉 What You've Accomplished

Your budget sheet now totals, averages, counts, flags over-budget rows, and stamps itself with today's date — and it all recalculates the instant you change a number. You've gone from writing arithmetic by hand to commanding Excel's built-in workhorses, including the decision-making power of IF. These functions will appear in nearly everything you build from here.

❓ Common Questions at This Stage

🔭 Looking Ahead

In the next lesson — Lesson 2.3: Text & Date Functions — TEXTJOIN, LEFT/RIGHT/MID & Working with Dates — we turn functions loose on words and dates. You'll join and split text (combine first and last names, or pull them apart), clean up messy entries, and learn why Excel stores dates as numbers — which is exactly what lets you calculate "days until due" the way you glimpsed with TODAY() here.

✅ Before the Next Lesson

• Keep your budget sheet with its SUM, AVERAGE, COUNT, IF, and TODAY() in place
• Make sure you can write an IF from scratch and read its three arguments aloud
• Write your Learning Journal entry for this lesson

📚 Additional Resources

• Excel functions (alphabetical) — Microsoft Support
• IF function — Microsoft Support
• microsoft365.com — open Excel for the web