The ultimate crash course in Excel – part 4

*Please enable cookies in your browser to watch the videos*

Maths…

Wait, before you run for the hills!

There’s no way around it: you can’t get what you want out of Microsoft Excel without a basic understanding of Maths. Luckily, that’s what today’s lesson is about.

At the heart of any Excel spreadsheet are the numbers within the data. Using basic maths functions to manipulate those numbers is one capability that makes Excel so powerful.

Calculations can be entered into the Excel formula bar or into a cell. It’s also possible to build a calculation in Excel by using the values in multiple cells.

All functions and formulae in Excel start a calculation with the = sign.

You can type the calculation you want to perform directly into the cell or the formula bar and when you press Enter the answer will show in the cell.

Another option is to use multiple cells to construct the formulae, as we see here (where cell A1 (or 87) plus cell A2 (or 16) equals cell A3 (or 103)):

Precedence (+ – * / )

When working with numbers and calculations in Excel, operators specify the type of calculation you wish to perform. The usual rules for the order of calculations apply in Excel, but you can change this order by using parentheses in your formula. In order to perform the basic mathematical operations such as addition, subtraction, multiplication, or division we use the following arithmetic operators:

  • + (plus sign) for addition
  • – (minus sign) for subtraction
  • * (asterisk) for multiplication
  • / (forward slash) for division

The following video shows these operators in action.

 

Excel interprets the = (equals) sign as indicating a calculation to be performed and calculates according to the operators indicated from left to right. To change the order in which Excel performs the calculations, enclose in parentheses the part of the formula to be calculated first.

For example if you have a formula: =6-3*4

You can use parentheses to make sure the 6-3 is calculated before the multiplication: =(6-3)*4

Powers

You can use a basic formula to create calculation exponents in Excel. To do so, use the = sign and the ^ (caret) symbol:

=3^2 OR =32^10

If the result of your calculation is very long, Excel will show the answer in scientific notation (as seen below):

Practice makes perfect, so download the exercise below and give it a go: Download Practice Document

Maths formulas

There are specific formulas that will perform maths functions in Excel. The first is SUM which adds up a group of cells:

Counting

Counting entries in spreadsheets can be done in a similar way to ‘summing’ them.  There are a few subtleties which we cover here.

Counting numbers

The COUNT() function counts the number of cells that contain numbers. That includes cells that Excel recognises as date-format. The function won’t count cells that contain text.

Example:

In the sheet below, the formula in A1 is       =COUNT(C1:H3)

This counts how many of the 18 cells (three rows by six columns) that are specified contain a number – so the answer is 6.

As with SUM(), COUNT() works with any number of inputs, and those inputs can be ranges and the Status Bar can be used to COUNT() the number of selected cells containing numbers. However, on the Status Bar COUNT will count the number of cells containing numbers or text. To count just numbers on the Status Bar, we need to customise it to ‘Numerical Count’.

Take care when using COUNT(). If numbers are forced to be interpreted as text (by specifying text format before entering a number for example), they will not be counted. We need Excel actively to convert the content to a number for COUNT() to recognise the entries as numbers—simply changing the cell format from Text to Number won’t rectify the problem:

Counting anything at all

The COUNTA() function counts the number of cells that have any content.  Replacing COUNT() with COUNTA() in the example above means the two cells with text content are also counted:

Example:

The formula in A1 is now =COUNTA(C1:H3)

 

This counts how many of the 18 cells that are specified as inputs to the function contain any entry – so the answer is 9.

Note: cells containing only spaces (so they appear blank) will still be counted by COUNTA().

Absolute references

The final, and very important function you need to understand in Excel when performing maths calculations are absolute references, this video explains it all:

There we have it – your crash course in Excel maths. That wasn’t so painful was it? Again, there are two more downloadable exercises for you to practice with below, enjoy!

Totaling a column and copying the formula to total another column: Download Practice Document

Adding a formula to count cells (containing numbers) in a range; then a formula for counting cells with any contents. Download Practice Document

AAT students and professional members can access a wide range of Excel resources and training.

Filtered provides algorithmically personalised Excel courses written by subject matter expert.

Comments

Related articles