Excel tips: Ultimate crash course series
- Part 1 – Copy, cut & paste in Excel
- Part 2 – Conditional formatting in Excel
- Part 3 – Pivot Tables in Excel
- Part 4 – Mathematical Formulas in Excel
- Part 5 – Excel formatting guide for a great looking spreadsheet
- Part 6 – Essential Excel formulas like vlookup
- Part 7 – Filtering your data in Excel
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
PowersYou 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):
Maths formulasThere are specific formulas that will perform maths functions in Excel. The first is SUM which adds up a group of cells:
CountingCounting entries in spreadsheets can be done in a similar way to ‘summing’ them. There are a few subtleties which we cover here.
Counting numbersThe 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 allThe 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 referencesThe final, and very important function you need to understand in Excel when performing maths calculations are absolute references, this video explains it all:
Filtered provides algorithmically personalised Excel courses written by subject matter expert.