Formulas, arithmetic operators and parentheses

The idea

Say the café owner from module 1 wants to know which products are actually worth selling. Knowing the price isn't enough for that. A cup of Kopi Susu sells for 25000, but if the milk, coffee and cup cost 15000, only 10000 of it is profit. And a product with a big price can still be a bad deal if most of that price goes to costs.

Questions like these are what most business formulas answer, and the surprising thing is how simple the maths usually is. Profit is price minus cost. Revenue is price times quantity. Margin is profit divided by price, which tells you what share of each sale you get to keep. It's all school arithmetic, applied to one row at a time.

So this module is about the arithmetic symbols Excel understands, and about one thing that trips people up more than you'd expect: the order Excel does the calculations in. Once you're comfortable with both, you can build almost any report.

The arithmetic operators

These are the symbols you use for maths in a formula. Most of them are the same as on a calculator. The two that might be new are ^ for powers and % for percentages:

The sheet for this module is the café's price list, now with what each product costs to make, how many were sold, and any discount:

In practice you'll rarely type numbers like 10+5. Instead you combine cell references, so the formula works on whatever is in the sheet. Here are the three questions from the start, answered for Kopi Susu:

F1: Profit per unit
F2: =B2-C2
G1: Profit
G2: =F2*D2
H1: Margin
H2: =F2/B2

F1: Profit per unit
F2: 10000
G1: Profit
G2: 120000
H1: Margin
H2: 0.4

Reading them one at a time: F2 takes the price (25000) and subtracts the cost (15000), so each cup earns 10000. G2 multiplies that by the 12 cups sold, for 120000 in total. H2 divides the 10000 profit by the 25000 price, giving 0.4. That means 40%: forty rupiah of every hundred the customer pays is profit. (Module 9 shows how to display it as 40%.)

Notice that G2 and H2 use F2, the result of another formula. That's completely normal. Building a calculation in small steps, each in its own column, makes it much easier to check than one giant formula.

Building a formula by pointing

You rarely type references by hand in real Excel. The fastest way to build =F2*D2 is:

1. Click the cell for the result and type =.
2. Click F2. Excel writes F2 for you and draws a coloured border around it.
3. Type *.
4. Click D2.
5. Press Enter.

Pointing avoids typos like =F2*D3. To check a finished formula, select the cell and press F2: every reference lights up in its own colour, with a matching coloured box around the cell it points at. Formulas → Show Formulas (Ctrl + \`) switches the whole sheet to show formulas instead of results, which is a quick way to review a sheet someone else built.

Order of operations

Here's where formulas start to go wrong in ways that are hard to spot. You might expect Excel to read a formula from left to right, like a sentence. It doesn't. It follows the same rules you learned in school maths, where some operations always happen before others, no matter where they are in the formula:

1. - as a sign (negation), as in -5
2. % percent
3. ^ power
4. * and / (left to right)
5. + and - (left to right)
6. & joining text (module 8)
7. comparisons like =, <, > (module 6)

So =2+3*4 is 14, not 20, because the multiplication happens first: 3 times 4 is 12, then 2 is added.

That sounds like a small detail, but look at what happens when you try to work out the total profit on Kopi Susu in a single formula:

# Without parentheses: the cost of all 12 cups is taken from the price of one
I2: =B2-C2*D2
# With parentheses: profit per cup, times 12 cups
I3: =(B2-C2)*D2

I2: -155000
I3: 120000

The first formula looks reasonable at a glance, but Excel does the multiplication first. It works out C2*D2, the cost of all twelve cups (180000), and then subtracts that from the price of one cup (25000). The result, -155000, makes no sense, and the scary part is that Excel doesn't warn you. It's a perfectly valid formula that answers the wrong question.

The second formula puts the subtraction in parentheses, which tells Excel to do it first. So it works out the profit per cup (10000) and then multiplies by twelve cups, giving the 120000 you got earlier with the two-step version.

Parentheses

Parentheses force part of a formula to be calculated first, before anything outside them. You can also put parentheses inside other parentheses when you need to. Excel colours each pair differently while you type, which helps you see which opening bracket goes with which closing one.

Discounts are the classic place where you need them. If something is 10% off, the customer pays what's left after the discount, which is 90% of the price. As a calculation, that's the price times 1 - 10%, because 1 stands for "the whole price" and you take 10% away from it:

# Kopi Susu's price after its 10% discount
J2: =B2*(1-E2)
# Teh Tarik's price after its 0% discount
J3: =B3*(1-E3)

J2: 22500
J3: 18000

Kopi Susu ends up at 22500, which is 25000 with 2500 taken off. Teh Tarik has no discount, so 1-E3 is just 1 and the price stays 18000. Without the parentheses, =B2*1-E2 would work out 25000*1 first and then subtract 0.1 from it, giving 24999.9, which is clearly not a 10% discount.

Powers and percentages

^ raises a number to a power, which means multiplying it by itself a number of times: 10^2 is 10 × 10. You won't use it every day, but it's exactly what you need for growth that builds on itself. If the price goes up 10% a year, after one year it's the price times 1.1. After the second year it goes up 10% again, but this time from the new, higher price, so it's the price times 1.1 times 1.1, which is 1.1^2:

# The price after two years of 10% growth
K2: =B2*1.1^2
# 10% of the price
K3: =B2*10%

K2: 30250
K3: 2500

After two years, Kopi Susu would cost 30250, not 30000. The extra 250 is the second year's 10% being charged on the first year's increase too.

% after a number simply divides it by 100, so 10% is a friendlier way to write 0.1. K3 is 25000 × 0.1, which is 2500.

When a formula can't calculate

Sometimes a formula asks Excel to do something impossible, like dividing by zero. Instead of guessing, Excel shows an error value in the cell. These look alarming at first, but each one is a short message telling you what kind of problem it ran into:

# Dividing by zero
L2: =B2/0
# Doing maths on text (A2 is "Kopi Susu")
L3: =A2*2

L2: #DIV/0!
L3: #VALUE!

Errors also spread. Any formula that uses the cell with =B2/0 will show #DIV/0! too, and so will anything that uses that one, all the way up to your grand total. It can be annoying to see a whole report fill up with errors because of one cell, but it's actually helpful: a problem can't quietly hide inside a total. When you see an error, follow the formulas back until you find the cell where it starts, and fix it there. Module 6 shows IFERROR, for the cases where you know an error will happen and want to show something nicer instead.

Recap

• Operators: + - * /, ^ for powers, % for percent.
• Excel applies ^ before * and /, and those before + and -.
• Parentheses change the order: =(B2-C2)*D2, =B2*(1-E2), =(B2+C2)/2.
• Refer to cells instead of typing their values.
• #DIV/0!, #VALUE! and #NAME? tell you why a formula can't calculate.