Using Arithmetic Formulas
Arithmetic formulas are the most commonly used formulas in Excel. They combine numbers, cell addresses, and function results with mathematical operators to perform calculations.
Mathematical operators are symbols that denote mathematical functions such as addition, subtraction, multiplication, division, etc.
|
Symbol |
Function |
|---|---|
|
+ |
Addition |
|
- |
Subtraction |
|
* |
Multiplication |
|
/ |
Division |
|
^ (Shift-6) |
Exponentiation (raised to the power of e.g., 3²) |
|
% |
Percent when typed after a number |
Let’s see how Sean is adding up the total marks scored by each student.
To add the total marks scored by John,
1. Select cell I3 and click inside the formula bar.
A blinking cursor appears inside.
2. Press the = (equal to) key on the keyboard.
The = sign is inserted in the formula bar. This sign tells Excel that you wish to enter a formula in the selected cell. Without clicking anywhere else, go to the next step.
3. To obtain the total of marks shown in cells C3, D3, E3, F3 and G3,H3 type the following formula in front of the = sign: (C3+D3+E3+F3+G3+H3).
The formula inside cell I3 should read = Sum (C3+D3+E3+F3+G3+H3).
Or =Sum(C3,D3,E3,F3,G3,H3)
Or =Sum (C3:H3)
4. Press Enter.
- The total mark of 450 appears in the cell.
- Cell I3 will now become deselected, and cell I4 will becomeselected.
5. To calculate Harry’s total marks, select cell I4 and click inside the formula Bar.
The blinking cursor appears inside.
6. In cell I4, type = (C4+D4+E4+F4+G4+H4) or the same formula used to calculate John’s marks.
Or Drag the Fill Handle
7. Press Enter.
The total mark of 470 appears in the cell.
8. You can accordingly find the total marks obtained by each student
NOTE: All Excel formulas begin with the = sign, and follow a specific order of operations:
- Exponentiation ^
- Multiplication and Division (* /)
- Addition and Subtraction (+ -)
- Figures in parenthesis ( ) are calculated first
Activity:
1. What is the answer to the following formula =8+3/4-2? What would you do if you wanted 8+3 to be calculated first? What would the answer be this time?
2. Open a new worksheet. In cell A1, type =27+3/3. In cell A2, type, =39*9-5. In cell A3, type =5^5*2. You should get the answers 28, 346, 6250 respectively.
TIP: Rules for Writing Excel Formulas
1. Formulas start with the = sign.
2. Use cell references in the formulas. (E.g. =A4+B4)
3. Follow the Order of Operations
