Panopia.com
Home
Calc
Archives

Formulas and Functions

Formulas

A formula takes the values in cells and performs caluculations on it, then returns a result. A simple formula would be one that takes the numbers in 2 cells and adds them together:

The formula in C1 is adding the contents of cell A1 to the contents of cell B1. If the numbers are changed in A1 or B1 the formula in C1 would recalulate the answer. That's the advantage of formulas, you write them once and if the data changes the results will reflect the change.
All formulas start with an equals sign. When an equal sign is typed into a cell the cell goes into edit mode, it's waiting for a formula. To refer to another cell in a formula you click on the cell and the reference is added to the formula. In the above example the steps to add the formula to cell C1 would be:

If you need to go back to a formula to change something you can double-click on the cell to put it into Edit mode again. If you decide not to change anything and want to get out of Edit mode press the escape key.

Functions

Most formulas will contain functions. One way you can put a function into a cell is with the Function Wizard. First click the cell where you want the function, then bring up the wizard by pressing the Function Wizard button on the toolbar:

In the first textbox you give a description of what you want to do like 'Add numbers' Then click the Go button, and the wizard will give you suggestions as to what function to use, you can also look up a category of functions to choose one that's right for what you're trying to do. The wizard will then take you step by step through how to set up the function and then place it in the cell.
In these lessons I won't be using the wizard, while it's very helpful, you really need to know what function your looking for and how to use it, this is best done by working with some basic functions and seeing how they manipulate the data in cells.
In the following example I have some numbers I want to add, the formula will be placed in cell B5:

To do this the sum function will be used, This function simply adds a list of numbers. Click in B5, then type the equal sign then sum as you type the word sum, the auto complete pop up will try to guess which function you want and offer a list of suggestions you can click sum in the list when you see it or keep typing until it's highlighted and then press the Tab key.

Excel will then put the function name after the equal sign and then an open parentheses.

Inside the parentheses of a function goes it's arguments or sometimes called parameters, these give the function the information it needs to complete it's task. In this example the sum function needs to know which numbers to add. In every function a popup will appear to tell you what argument it wants, here it's asking for number 1 which is in bold type to indicate that's the current argument it wants. To add a range of cells to a formula drag and highlight the cells. A rectangle of moving dots will appear around the range you selected.

In the formula the range of cells is indicated by a colon between the first and last cell selected, this means from A1 to A4. This is the only argument needed for the sum function. The pop up indicates there are more arguments which are optional.
[Number 2]...
When an argument is placed in brackets it means that it's optional. for instance, if there was another set of numbers to add, a comma would be typed to get to the next argument, and another range selected.
To complete the formula press Enter. You do not need to add the closing parentheses when there is only one function in the formula.
Formulas can contain several functions and functions can contain other functions in thier arguments. Basic Math operations are done using the following operators:

Operation Operator
Addition + (plus sign)
Subtraction - (minus sign)
Multiplication * (asterisk)
Division / (forward slash)
Percentage % (percent sign)
Exponentiation ^ (caret)

When combining several operators in a one formula the operations are evaluated from left to right. Excel will first perform negation for any values with a minus sign preceding them as in -10 or a cell reference with -A1. then it will perform math operations in the following order of precedence:

Operator Notes
% (percentage sign) Percentage
^ (caret) Exponentiation
* (asterisk) and / (forward slash) Multiplication and Division
+ (plus sign) and - (minus sign) Addition and Multiplication

To change the order that Excel evaluates operations use parentheses around the operations that need to be calculated first. for example an addition which needs to be done before multiplication:
=10*(6+5)

©2021 Panopia.com