Google Sheets Course: Formulas
Calculation Formulas
Always start your formula with a =
, then add a number, then an operator (for example +), then a number, etc. Add ( )
when necessary.
Examples:
=45-35
(the displayed result will be: 10)
=(45-35)^2/8
(the displayed result will be: 12.5)
Here are the useful operators for your calculations:
Operator | Use | Example | Result |
---|---|---|---|
+ | Addition | =50+5 | 55 |
- | Subtraction | =50-5 | 45 |
* | Multiplication | =50*5 | 250 |
/ | Division | =50/5 | 10 |
^ | Exponent | =50^5 | 312500000 |
You now know how to write a formula by directly entering the numbers in the formula but in most cases, you will need to enter cell references instead of directly entering numbers.
To better understand the usefulness of entering cell references, observe this example:
In this case, the numbers have been entered directly in the formula =50*60
.
The problem is that if you now change the values of cells A2 or B2, you will have to think to modify the formula with each change and this is really not practical:
That's why you should refer to the cells instead of directly entering the numbers in the formula =A2*B2
:
In this way, when you modify cells A2 or B2, the result of the formula will be automatically recalculated:
Copying Formulas
As you have seen in the previous lesson, copying works very well with formulas:
The starting formula at line 2 is =A2*B2
and the copy ends at line 6 with the formula =A6*B6
:
But in some cases, you will need to reference a unique cell...
To better understand, a divisor is now added to the formula =A2*B2/B8
and the formula is copied:
This time, it won't work because the line of the divisor has been moved in the same way as values 1 and 2:
To fix a reference during a copy, add $ to the reference.
The formula =A2*B2/B8
then becomes =A2*B2/$B$8
:
After copying, all the formulas now refer to cell B8: