Utah Community Learning

Plus, minus, times, divide, and pointing at other cells

Last time we did SUM, and I hardly stopped talking about it. Today we widen out a bit. Addition is fine, but it isn't the only math you've got, and once you see how the pointing part works, you can do all four.

A formula in a spreadsheet is really just two ideas set side by side: pick an operator, and point at cells instead of typing numbers. Once that settles in, the rest is just which symbol you use.

The equals sign is the whole trick

Every formula starts with =. That's how the sheet knows you're not just typing a number or a word, you're asking it to work something out.

Then you build it up with these:

+ for adding
- for subtracting
* for multiplying
/ for dividing

So =4+4 gives you 8. Nothing to write home about yet. The good part is next.

Point at cells instead of typing numbers

Say you've got a number in A1 and a number in A2. Instead of typing =4+4, click into A3, type =, then click on A1, type +, then click on A2, press enter.

You'll see =A1+A2 appear in the formula bar. That's it. That's the whole move.

Why does this matter more than it sounds like it should? Because now if you change the number in A1, A3 updates on its own. You didn't retype a thing. That's the point of this module, right there in a single sentence. If you're typing the same number into two places, you've already planted a mistake for later, you just haven't met it yet. It's only a matter of when you find it.

Try all four at home

Open a mostly blank sheet, or use one you've already got going.

Put a number in A1, a number in A2.
In A3, type =A1+A2 and press enter.
In A4, type =A1-A2 and press enter.
In A5, type =A1*A2 and press enter.
In A6, type =A1/A2 and press enter.

Now go change the number in A1. Watch all four update at once. That little jolt of "oh, it just did that" is close to the reason I do this for a living.

One real caution here: if A2 is zero and you're dividing, you'll get an error that looks like #DIV/0!. That's not broken, that's the sheet rightly telling you dividing by zero doesn't work. Don't fret, don't retype the whole formula, just go check what's in that cell.

You can mix operators too

You're not stuck with just one. =A1+A2*A3 is a real formula. Just know that multiplication and division happen before addition and subtraction, the same order of operations you learned in school and maybe resented. If you want to force a certain part to happen first, wrap it in parentheses: =(A1+A2)*A3.

I won't pretend I always remember the order without thinking. When I'm not sure, I add parentheses around the part I mean first. Costs nothing, saves a headache.

A quick aside on being confidently wrong

I'll admit it, I check my own work a lot doing this job, because it's easy to sound sure about something you're actually fuzzy on. A coworker once told me flat out that VLOOKUP can't look to the left of its search column, as if it were gospel. He was wrong about the mechanism, there's a way around it, but he was right that it's a bother and most people don't trouble with it. I told him so. Both things were true at once. I bring it up because formulas are one of those places where it's easy to nod along like you follow and then go home and just retype numbers anyway. If a formula isn't doing what you expected, that's not you being bad at this, that's the ordinary way you find out what a formula actually does.

Fixing a wrong pointer

If you click the wrong cell while building a formula, don't start over. Just click into the formula bar, delete the wrong cell reference, and click the correct cell instead. The sheet doesn't mind that you fumbled. Neither should you.

This is also a fine excuse to leave something broken on screen if you're practicing with someone else, a spouse, a child, whoever, and ask them to find where the pointer's aimed wrong. It's a genuinely good way to learn this. Far better than watching me do it clean.

Before next time

Build one small formula at home that points at two real numbers you care about, groceries, gas, whatever you've got sitting in a sheet already. Change one number and watch it ripple through. That's the whole lesson, just make it happen with your own numbers once.