8. Applying operations to (long) lists of data


Now that you know how to perform simple operations AND how to fill in series of cells, you will be able to apply operations to long lists of data in a few clicks. Let’s assume that you want to multiply a whole series of numbers ranging from 1 to 10 by 5. First, create the series in the area A1:A10. Then, type in the operation “=A1*5” in B1, press ENTER, select B1, click on the square and drag the selection dow to B10. This should result in a series ranging from 5 to 50.

If you prefer to multiply the original series in A1:A10 by the content of a specific cell (for example C1), type in the operation “=A1*$C$1” in B1, press ENTER, select B1, click on the square and drag the selection down to B10. A series of zeros appears in B1:B10, essentially because the cell C1 is empty (which equals 0, by default). Fill in C1 with the value of your choice (let’s say 5), press ENTER and a series ranging from 5 to 50 magically replaces the bunch of zeros. Note that the values in B1:B10 will permanently depend on the content of C1. Changing the value in C1 will thus dynamically modify the content of B1:B10. For instance, typing 12 in C1 modifies the content of B1:B10 and a series ranging from 12 to 120 is now displayed. In fact, the symbol $ helps to “lock” the address of a cell. Pressing the F4 key on the keyboard after typing C1 in the formula bar adds the symbol $ in front of both C and 1. Pressing F4 twice adds $ only in front of 1. Pressing F4 three time adds $ only in front of C.

Note: when dragging a selection which contains a formula which refers to a specific cell, the address of the cell will be modified line after line and column after column. Dragging downward modifies the address by incrementing the letter (B1>C1>D1>E1>F1…) whereas dragging to the right modifies the address by incrementing the number (B1>B2>B3>B4>B5>B6…). The symbol $ locks the address of the cell so that the reference to C1 does not vary when dragging the selection to fill in cells. This ensures that the formula which you have entered in B1 always multiplies by the content of C1.

Omitting the symbol(s) $ allows the cell address in the formula to vary automatically with the direction towards which you drag the mouse. Dragging “=A1*C1” downward would thus results in “=A2*C2”, “=A3*C3” and so on… Dragging “=A1*C1” towards the right results in “=B1*D1”, “=C1*E1” and so on… This is useful for example when you want to apply an operation or formula to pairs of cells.

  Fant du det du lette etter? Did you find this helpful?
[Average: 0]