Simple Formulae in Microsoft Excel

#formulae #excel #adding #subtracting #multiplying #dividing #bidmas

By: Tony Gaskell

Simple Formulae

All calculations begin with an equals sign ‘=


Pressing Enter after a calculation has been written will show the answer.


Calculations will automatically recalculate if a value is entered or changed.


All text value in formulas must be in double quotes.

Adding

This will give the result of whatever is in A2 added to whatever is in B2 and will change if the values in those cells change.

You can continue to add as many cell references as you want to the formula, as long as there is a plus sign between each of them.


This will give the result of whatever is in A2 added to whatever is in B2 plus 5.

This will add all the numbers in the cell range A2 through to A5.


Start by typing in =SUM( and then select the range by clicking and dragging the mouse. It can be any contiguous range.


Then press the Enter key.

You can also add up columns or rows of numbers by clicking the cell underneath a column or the cell to the right of a row and pressing the AutoSum button on the Home Toolbar and then press Enter.

Subtracting

This works in exactly the same way as adding, although there isn’t a similar function to SUM.

Multiplying

This also works in the same way as adding, but the multiplication symbol is denoted by the asterisk * (obtained by pressing SHIFT and 8 on the keyboard).

Dividing

This also works in the same way as adding, but the division symbol is denoted by the forward slash / (next to the right SHIFT key).


Note that dividing by zero with give an error of #DIV/0!. This will also happen if the divisor cell is empty.

BIDMAS

Calculations in Excel explicitly follow the rules of BIDMAS/BODMAS.


BRACKETS – anything in brackets is calculated first.


INDICES/ORDER – any powers are calculated next.


DIVISION/MULTIPLICATION – calculations containing division or multiplication are calculated from left to right.


ADDITION/SUBTRACTION – finally, the additions and subtractions are calculated from left to right.


Therefore, you can force calculations to circumvent the rules of BIDMAS by enclosing them in brackets.


=12 + 3 * 7 will give the result of 33 because the multiplication is worked out first.


=(12 + 3) * 7 will give the result of 105 because the calculation in the brackets is processed first.


Note that usually the numbers will be cell references rather than actual numbers.


Recent Posts

See All