1.3.B REFERENCING A BLOCK OF CELLS

Select the worksheet “main.” Choose cell H236. In the chosen cell, type

the text shown in the next figure.

29

Statistical Analysis with Excel

Figure 10: This formula requires a block of cells as a reference

Use the mouse to highlight the block of cells “E223 to E235.” Type in a

closing parenthesis and press the ENTER key. The resulting function is

shown in the next figure.

Figure 11: Formula with a block of cells as the reference

1.3.C REFERENCING NON“ADJACENT CELLS

Choose cell H237. Click in the cell and type the text shown in the next

figure.

Figure 12: The core function is typed first

As in the previous example, choose cells E223 to E235 by highlighting

them” the formula should like the one shown in the next figure.

Figure 13: The first block of cells is referenced

Type a comma. The resulting formula should look like that shown in the

next figure.

30

Chapter 1: Writing Formulas

Figure 14: Getting the formula ready for the second block of cells

Highlight the block of cells “E210 to E222.” Key-in a closing parenthesis

and press the ENTER key.

Figure 15: The formula with references to two non-adjacent blocks of cells

1.3.D REFERENCING ENTIRE ROWS

Choose cell H238. In this cell, type the text shown in the next figure.

Using the mouse, highlight the rows 197 to 209. Type in a closing

parenthesis and press the ENTER key. The resulting formula is shown in

the next figure.

Figure 16: Referencing entire rows

1.3.E REFERENCING ENTIRE COLUMNS

Choose cell H239. In this cell, type the text shown in the next figure.

Using the mouse, highlight the columns C and D. Key-in a closing

parenthesis and press the ENTER key.

31

Statistical Analysis with Excel

Figure 17: Referencing entire columns

1.3.F REFERENCING CORRESPONDING BLOCKS OF

CELLS/ROWS/COLUMNS FROM A SET OF WORKSHEETS

Assume you have a workbook with six worksheets on similar data from

six clients. You want to sum cells “C4 to F56” across all six worksheets.

One way to do this would be to create a formula in each worksheet to sum

for that worksheet™s data and then a formula to add the results of the

other six formulae.

Another way is using “3“D references.” The row and column make the

first two dimensions; the worksheet set is the third dimension. You can

use only one formula that references all six worksheets that the relevant

cells within them.

While typing the formula,

• Type the “=“sign,

• Write the formula (for example, “Sum”),

• Place an opening parenthesis “(,” then

• Select the six worksheets by clicking at the name tab of the first one

and then pressing down SHIFT and clicking on the name tab of the

sixth worksheet, and then

• Highlight the relevant cell range on any one of them,

• Type in the closing parenthesis “)”

• And press the ENTER key to get the formula

=SUM(Sheet1:Sheet6!”C4:F56”)