Utah Community Learning

AVERAGE, MIN, MAX, and when you'd actually want them

We've got SUM down, and we've got the basic math operators down, so now let's add three more formulas to the toolbox. These three: AVERAGE, MIN, and MAX. They're small, they're quick to type, and they answer questions people ask me all the time without knowing there's a formula for it.

Why these three and not, say, twelve more

There are a hundred formulas in a spreadsheet program. You do not need most of them. You need the ones that answer questions you actually have.

AVERAGE tells you the typical value in a range. MIN tells you the smallest. MAX tells you the biggest. That's it. No trick to it beyond that.

AVERAGE

Say you've got a column of your electric bill for the last twelve months. You want to know roughly what you pay a month, not each separate bill, just the general "what should I budget" number.

Click into an empty cell below or next to your data. Type:

`` =AVERAGE(B2:B13) ``

Swap B2:B13 for wherever your numbers actually live. Press enter. Done. That's your average.

One thing to watch: if you've got a blank cell in that range because you forgot to log a month, AVERAGE just skips it, it doesn't count it as zero. That's usually what you want, but it's worth knowing, so a gap in your data doesn't quietly throw off the number without you noticing.

MIN and MAX

Same idea, same shape of formula. MIN finds the smallest number in a range, MAX finds the biggest.

`` =MIN(B2:B13) =MAX(B2:B13) ``

For that electric bill column, MIN would tell you your cheapest month, MAX tells you your most expensive one. Around here that's usually a summer month for the air conditioning, or a January when it's cold enough up the canyon that the furnace runs without a rest. I'll admit our place has a January spike every single year and I still act surprised.

Where this actually gets useful

Here's where I want you to slow down for a moment, because this is the part people skip. Don't just slap these formulas onto a column because they exist. Ask yourself what you're trying to learn first.

If you're tracking grocery spending, AVERAGE tells you your normal week. MAX tells you the week you overspent by a good bit, which helps, because now you can go look at what happened that week. Maybe it was a Costco run that stocked you up for a month, in which case the "overspend" isn't really bad, it's just lumpy. That's the sort of thing a plain list of numbers won't tell you but one formula will.

If you're tracking something like a child's naps, or workout times, or how long a recipe actually takes you at our elevation versus what the box says, these three formulas start doing real work in a hurry.

A quick real one from my house

I went through a stretch a while back where I had three Relief Society things back to back in one week, on top of the usual chaos with the children, and by Thursday I had nothing left. I ended up canceling plans with a friend by text because I simply didn't have anything in the tank. I keep a note now, right in the front of my birthday notebook, that just says "batteries," so I remember I'm not endless and I need to watch for that instead of pretending it doesn't happen.

If I'd been tracking my week somehow in a sheet, hours committed versus hours I actually had, AVERAGE would've told me I was already over my normal load by Tuesday, and MAX would've shown me exactly which week broke me. I don't track it that closely, that might be more sheet than the problem calls for. But I think of it every time I teach this formula, because it's a good example of numbers that would have warned me if I'd been looking.

Try it yourself right now

Open whatever sheet you've got going. Pick any column of numbers, it doesn't matter what it is.

Click an empty cell below the column.
Type =AVERAGE( and then click and drag over your numbers, or type the range like B2:B13.
Close the parenthesis, press enter.
Do the same with MIN and MAX in the cells below that.

Look at the three results together. Does the average feel right, based on what you already know about that data? If it feels way off, check that you selected the right range. This is the same kind of mistake we've talked about before, a formula quietly pointing at the wrong cells, running fine, giving you a wrong answer that looks like a right one.

Before next time

Pick one column of real numbers you already have in a sheet, it doesn't matter which, and run all three formulas on it. See if the MAX surprises you. Mine usually does.