|
|
 |
| Referencing Cells in Excel |
 |
Today I'd like to present a couple of exercises in referencing cells in formulae in Excel.
Exercise 1: calculate percents given counts
Ok, let's say that you have a list of responses, texts, etc. (column A)
with the respective number of occurrences (counts - column B):
| A | B |
| 1 | elem1 | 12 |
| 2 | elem2 | 10 |
| 3 | elem3 | 2 |
| 4 | elem4 | 3 |
| 5 | elem5 | 15 |
And you want to calculate, for each element in the list, the respective percentage in the whole number of occurrences:
| A | B | C |
| 1 | elem1 | 12 | 29% |
| 2 | elem2 | 10 | 24% |
| 3 | elem3 | 2 | 5% |
| 4 | elem4 | 3 | 7% |
| 5 | elem5 | 15 | 36% |
The first part is easy - to calculate percentages you need the sum of all counts.
The formula =SUM(B1:B5) will calculate the counts for all elements.
Then, you can use this formula, in column C, to calculate percents (as column B divided by the sum)... or almost:
| A | B | C |
| 1 | elem1 | 12 | 29% |
| 2 | elem2 | 10 | 33% |
| 3 | elem3 | 2 | 10% |
| 4 | elem4 | 3 | 17% |
| 5 | elem5 | 15 | 100% |
If you use copy & paste or if you enter the formula in row 1 and then drag that formula to fill all 5 cells in column C
(or use the shortcut Ctrl+D), Excel will automatically increment references in all generated formulae.
Thus, the actual formula in row 5 will be =C5/SUM(B5:B9) - and that's not what we want at all.
We can call these types of references to cells as relative references, that is references that get updated
whenever you copy formulae with them from one cell to other cells
(take a look at the bottom of this text for a fuller explanation).
OTOH, Excel provides us with absolute references, that is those references that are not changed on copy.
Note, however, that in the formula used above, the numerator should be a relative reference (you want C2 in row 2),
but the denominator should be an absolute reference - B1:B5 in all rows.
Relative references are written simply as column ID + row number (such as C3).
In such a reference, you can make either the column reference absolute, the row reference absolute,
or both references absolute. The dimension(s) that you want to make absolute should be prefixed with a dollar sign.
For instance, C$3 means that when copying this formula from one cell to another, the column reference (C) will be updated,
while the row reference (3) will remain as is.
Now the solution to the percentages problem above should be simple.
Before copying, change the formula from =C1/SUM(B1:B5) to =C1/SUM(B$1:B$5)
making row references in the sum absolute - in row 5 you should get the formula =C5/SUM(B$1:B$5).
Note also, that in this case, you could make all column references absolute and things would still work.
It's really easy to make mistakes when writing these kinds of formulae (and this one is really simple).
So it's a good practice to add some test that verifies the correctness of all such calculations, even as simple as the one above.
Here, a good check would be to count all percents - add a =SUM(C1:C5) in row 6 - we should get 100%:
| A | B | C |
| 1 | elem1 | 12 | 29% |
| 2 | elem2 | 10 | 24% |
| 3 | elem3 | 2 | 5% |
| 4 | elem4 | 3 | 7% |
| 5 | elem5 | 15 | 36% |
| 6 | sum | | 100% |
Exercise 2: the multiplication table
A really nice exercise to practice working with relative and absolute references is the multiplication table:
|
A |
B |
C |
D |
E |
F |
| 1 |
|
1 |
2 |
3 |
4 |
5 |
| 2 | 1 | | | | | |
| 3 | 2 | | | | | |
| 4 | 3 | | | | | |
| 5 | 4 | | | | | |
| 6 | 5 | | | | | |
Before writing the formula note that:
- you are always referencing the first row in consecutive rows,
- you are always referencing the first column in consecutive columns.
Keeping that in mind, the formula should come pretty easily.
For those of you who hate (as I do) exercises without correct answers,
the formula that needs to be entered in the first cell (i.e. B2) and copied to all others is... =$A2*B$1.
And voila!
|
A |
B |
C |
D |
E |
F |
| 1 |
|
1 |
2 |
3 |
4 |
5 |
| 2 | 1 | 1 | 2 | 3 | 4 | 5 |
| 3 | 2 | 2 | 4 | 6 | 8 | 10 |
| 4 | 3 | 3 | 6 | 9 | 12 | 15 |
| 5 | 4 | 4 | 8 | 12 | 16 | 20 |
| 6 | 5 | 5 | 10 | 15 | 20 | 25 |
Have fun!
A note on terminology
In the examples above references were called absolute and relative.
C3, for instance, looks like an absolute reference
(it points to a specific cell regardless of the cell the formula is in),
so you might wonder why it's called a relative reference.
Actually, references to cells are internally kept in a bit different format: RnCm (row n, column m).
(You can also use this format if you change a setting in Excel's Options dialog window:
go to the General tab and mark the Reference Style R1C1 check box).
There are two formats for referencing a row / column with a given index:
-
Rn (or Cn) to reference an absolute index (row n / column n);
here, n is greater that 0.
-
R[n] (or C[n]) to reference an index relative to the current index;
here, n may be less than or greater than 0.
It may also be 0 (or just R or C) to reference the current row / column.
So, a reference $C$3 will be changed to R3C3 internally, and that is an absolute reference.
But C3 will be changed to... Right, we don't know - this depends on the cell the formula is in.
If we're in $C$3, then C3 will be changed to R[0]C[0] (or RC, actually).
If we're in $D$2, then C3 will be changed to R[-1]C[1].
Then, if we copy C3 from cell $D$2 to cell $F$1, it still is understood as R[-1]C[1],
thus the relative reference changes from C3 to E2.
Top
|
| Comments |
Alas!
No comments yet...
|
|
Top |
|
Top
|
|
|
Texts
