Force Byte
Knowledge and Techniques of Data Input in Excel (10)

0 Photo

In the previous example, we apparently could apply the MAX function used in cell B4 to range C4:F4 to get the maximum values of the other four types of cases. However, I will teach you an even simpler method to achieve the same goal in this episode. That is to copy the formula in cell B4 to range C4:F4. The steps are as follows:

1. Click cell B4.



2. Move the cursor to the fill handle at the bottom right corner of cell B4. The cursor will now change into the shape of a "cross".

3. Drag the fill handle all the way through C4:F4.



4. Release the mouse button. Click any cell to cancel the previous selection (click cell F4 in this example) and the following result is obtained.



You may find that the value in cell B4 is the result of Max (B2:B3). After the copy, you get the following results.

C4=Max (C2:C3)

D4=Max (D2:D3)

E4=Max (E2:E3)

F4=Max (F2:F3)

Have you wondered why the results of the copy do not look like what you thought as shown below?

C4=Max (B2:B3)

D4=Max (B2:B3)

E4=Max (B2:B3)

F4=Max (B2:B3)

The reason is that we are using the concept of "relative reference" in calculating the maximum value in cell B4. That means the content of cell B4 is obtained by calculating the biggest value in the second and third cells above it. When we copy the formula, we also copy this concept to range C4:F4. Hence, the result of the copy is illustrated as follows:



In addition, the method to calculate the maximum values for range D4:F4 is the same as the above.

You should remember that when we use the "column and row labels" to stand for the cell addresses in the formulas, e.g. using B4 to stand for the cell in column B and row 4, we are actually using the "relative reference" concept. With this concept, Excel looks for other cells referenced in the formula relative to the cell containing the formula.

Lastly, there are actually two other types of address references in Excel, namely "absolute reference" and "mixed reference". I will continue to elaborate on these concepts in the coming issue.

"Sharing IT as it applies to your daily life."

(E-mail address: ITB_ForceByte_Editor@police.gov.hk)
<<Back to Features>> <<Back to Top>>