Microsoft Excel 2007 - Using a formula
Labels and values
The entering of data into a spreadsheet is just like word processing, but you have to first click on the cell in which you want the data to be placed before typing the data.
All words describing the values (numbers) which you enter are called labels. The numbers which you enter, and which can later be used in formula's, are called values.
Notice also that the labels are all left justified and the values are all right justified in their cells.
Place the cursor in the cell in which you want the answer (result of the formula) to appear, and press Enter once you have typed the formula
All formula's start with an = sign
Refer to the cell address instead of the value in the cell
e.g. =A2+C2 instead of
45+57
| + means add | e.g. A2+C2 | add the value (number) in A2 to the value (number) in C2 |
| - means subtract | e.g. A2-C2 | subtract the value (number) in C2 from the value (number) in A2 |
| * means multiply | e.g. A2*4 | multiply the value (number) in A2 by 4 |
| / means divide | e.g. A2/3 | divide the value (number) in A2 by 3 |
Use BODMAS i.e. Brackets first, then Division, Multiplication, Addition and Subtraction
e.g. =((A2-B2)*3)-100
Notice that individual sums within a sum are bracketed and appear at the beginning of the formula.
These formula's are used for working with long lists of numbers. A typical range formula looks like this:
=SUM(A3:A30)
SUM is a function, meaning that it sums
(adds up) a list or range of numbers
The range of numbers is indicated in brackets.
The address of the first cell in the range is A3.
A colon : separates this first cell address from
the last cell in the range, which is A30
=SUM(D3.D9)
Adds list of values from cell D3 to cell D9
=AVERAGE(D3:D9)
Averages the values from cell D3 to cell D9
Note: a short cut would be to select the column of figures, and an empty cell for the total, andthen to click on the sum icon 
.
Other range functions:
There are many formula functions that you could explore with time. The SUM and AVERAGE formulae are most commonly used when working with marks. Here are just a few more:
| SUM | adds the numbers in the list |
| AVERAGE | averages the numbers in the list |
| PRODUCT | multiplies the numbers in the list |
| MAX | identifies the highest number in the list |
| MIN | identifies the lowest number in the list |
| COUNT | counts the number of numerical items in the list |
| COUNTA | counts the number of alphabetical items in the list |
| COUNTIF | counts the number
of items that satisfy certain criteria e.g. =COUNTIF(A4:A20,">50") counts the number of values that are higher than 50 in the list from A4 to A20 - notice the use of the comma and quotation marks to separate the list (A4:A20) from the criteria (>50). |
Notice that you must have selected
an empty cell before typing the formula. The answer will
then appear in that cell.
Absolute values in a formula
When you copy a formula like =B5/B2 from Row 5 to Row 6, the row indicators (5 and 2) in the formula change automatically, so that the copied formula would then be =B6/B3
Similarly if you copy =B5/B2 from Column B to Column C, the column indicator (B) in the formula changes to C5/C2
If you want to prevent this column or row change from happening, you should place a $ sign in front of the row or columns indicator.
e.g. If you want B2 to remain unchanged, the formula should be written as =B5/$B$2
i.e. neither B nor 2 must change as the formula is copied.
All Rights Reserved.