How to Use Basic Math Functions in a Spreadsheet

This tutorial covers how to perform basic math operations and is aimed at beginning-level users of Excel and other similar spreadsheet programs such as LibreOffice Calc and Google Sheets.

Personally, I prefer using a spreadsheet over a calculator. The reason is simple. Spreadsheets allow me to scan for and correct typos much easier than do most calculators. This allows me to enter long series of numbers if needed.

The topics covered here are: addition, subtraction, multiplication, division, parentheses, exponents, and the square root.

Addition

Spreadsheets have two input methods when it comes to adding numbers.

Method 1

This table demonstrates one method for performing addition. Notice that like almost all spreadsheet formulas, it begins with an equal sign =.

The first column shows the formula syntax. The second column shows the result.

formula result
=2+3 5
=4+3+2 9
=2+3+4 9
Method 2

This table demonstrates the second method for performing addition. It uses a function, sum(). As they all do, this formula begins with an equal sign =. This is followed by the function name and parenthesis to contain values. In this case, the values are the numbers to be added. The values are separated by commas.

The first column shows the formula syntax. The second column shows the result.

formula result
=sum(2,3) 5
=sum(2,3,4) 9
=sum(4,3,2) 9

The way the second method is put together is a good example of a typical function, that is, it contains parentheses, with some values inside, and the values are separated by commas. Something else that is frequently found inside a function’s parentheses are parameters, but I will save that for a later discussion.

Subtraction

For subtraction, spreadsheets accept one syntax style.

The first column shows the formula syntax. The second column shows the result.

formula result
=3-1 2
=4-2-1 1

Multiplication

Like addition, spreadsheets accept two statement types for performing multiplication.

Method 1

This table demonstrates one way to do multiplication.

The first column shows the formula syntax. The second column shows the result.

formula result
=2*3 6
=4*3*2 24
Method 2

This table demonstrates the second way to perform multiplication. It uses a function. The function is product(). The numbers inside the parentheses, values, are what is getting multiplied. Or to put it a bit more concisely, the function, product(), is being applied to some values in the example shown.

The first column shows the formula syntax. The second column shows the result.

formula result
=product(2,3) 6
=product(4,3,2) 24

Division

Division, like subtraction, is performed using only one style of syntax.

This table shows how to perform division.

The first column shows the formula syntax. The second column shows the result.

formula result
=4/2 2
=6/2/3 1

Parentheses

You may remember a concept, the order of operations, from math class. The mnemonic device many use to remember it is PEMDAS or Please Excuse My Dear Aunt Sally. This is to help you remember to first do parentheses (brackets too), exponents (and roots), multiplication and division (they are equal), and addition and subtraction (also equal). When equal items are encountered, you revert to “from left to right” order.

A math teacher friend suggested this music video:

Spreadsheets follow the order of operations. Let me illustrate how this works with parentheses.

The first column shows the formula syntax. The second column shows the result.

formula result
=(4-3)*2 2
=4-3*2 -2
=3/6-2 -1.5
=3/(6-2) 0.75
=((2+3)*3)/5 3
=2+3*3/5 3.8

Exponents

The character ^ is pronounced caret symbol. It is found above the number 6 on standard QWERTY keyboards. This is what is used to perform calculations using exponents on a spreadsheet.

This table demonstrates the use of exponents.

The first column shows the formula syntax. The second column shows the result.

formula result
=2^2 4
=2^3 8
=3^2 9
=3^3 27
=2^8 256

Square Root

I rarely have the occasion to use it myself, but no basic math tutorial would be complete without covering the square root.

Spreadsheets use the function, sqrt() for square roots.

The first column shows the formula syntax. The second column shows the result.

formula result
=sqrt(9) 3
=sqrt(144) 12
=sqrt(256) 16

Show some love! Did this tutorial work for you? Was it as clear as mud? Use the comment form. Ask questions, add a comment, or just share this post with someone you know that needs it.

If you find yourself in a pinch, you should know that I am available to help with spreadsheet projects.

Leave a Reply

Your email address will not be published. Required fields are marked *