Functions and Range Names

Instructions: You will edit the existing formulae to make them more readable.

Comment: Using range names in spreadsheets not only makes them easier to read but also much easier to manage. If you have several similar but not identical spreadsheets it is a great help if you do not have to know where your data is physically stored in order to access it.

Activity 9.1 Select cell F9. This currently houses a formula that does not make sense because it tries to add up three blank cells. We are going to enter in a formula to give the total for January. The formula could read like =SUM(C5:E8) or else we could write =SUM(F5:F8). However, instead of typing that, type:

=SUM(Jan)

Press <Enter>. The result, the total of all the numbers in the range named Jan, is displayed. Do the same for =SUM(Feb) in F14 and =SUM(Mar) in F19.

Activity 9.2 Now select C20. Move the cursor to the formula bar and select the range references in the brackets. Type the word Travel, and press <Enter>. The number stays the same, but now the formula makes much more sense - it's the average of the weekly travel bill.

Activity 9.3 We will now experiment a little. Select cell I6 and type the following:

=SUM(Travel Jan)

The result displayed is the total of the travel expenses in January. This is clear from the text of the formula.

It is possible to use range names in combinations to obtain references to subsets or supersets of the ranges themselves. The rules are as follows:

SUM(Range1 Range2) - separated by a space this gives the cells that are common to both ranges.

SUM(Range1, Range2) - separated by a comma and a space this gives the Union of the two ranges. Common cells are counted twice.

SUM(Range1:Range2) - separated by a colon (like most range references), gives the smallest rectangle of cells to encompass both ranges.

Activity 9.4 Try the following in column I:

I7 - =SUM(Travel)

I8 - =SUM(Jan)

I9 - =I7+I8

I10 - =SUM(Jan,Travel)

I11 - =SUM(C5:E18)

I12 - =SUM(Jan:Travel)

You should be able to spot the way these formulae are working from the alternative calculations given. When you are finished delete the formulae from the I column by selecting the range and pressing the <Del> key.

OSU | CoF | Community | Search | Forestry Computing Helpdesk