OpenOffice.org 2, Firefox, and Thunderbird for Windows All in One [Electronic resources] نسخه متنی

57. About Names as Addresses

49 About Calc Ranges

50 Create a Range

The makers of OpenOffice.org designed Calc to be watching over your shoulder, ready to help you when possible. One way that Calc does this is to look at the labels you place in your spreadsheets and use them as range names when appropriate.53 Enter Calc Functions , the

Sum button is useful when you are totaling a column or a row and the total is to appear directly at the end of that column or row.

Totals do not always fall at the end of a row or column.

[View full size image]

Of course, you could type the Sum() function in the appropriate cells of column B, but doing so (especially if the table were much larger) is time-consuming and error-prone. To total the Miami sales projections, for example, you would enter this into cell B4:

=Sum(H4; H10) 

Likewise, you would have to enter every other range for the rest of the totals.

One improvement would be to create and name the following ranges from the sheet: Est2005, Est2006, Est2007, Est2008, Denver, Tulsa, Miami, and New York. At least entering the Sum() functions would be simpler. For Miami's total, you would type =Sum(Miami) into cell B4. If you added data to the table, your range names would expand appropriately, keeping the totals accurate.

TIP

You rarely can name too many ranges. If you create a spreadsheet that you plan to add to quite a bit, create range names as you go so that subsequent formulas you enter will be able to rely on those names. For very small sheets, though, sometimes naming ranges is more trouble than its worth.

In spite of some advantages to the range names, naming six ranges for this small table is a lot of work. It's simpler and quickeralthough, as just stated, more error-prone and more difficult to maintainif you create the ranges individually for totals.

Fortunately, Calc is smart. Notice the headings over the columns of data: Denver, Tulsa, Miami, and New York. These are not range names; rather, they are just labels typed over the columns to label the data. The same is true for the rows with these labels: Est 2005, Est 2006, Est 2007, and Est 2008.

Although the column labels are not range names, you can often treat such column and row headings as though they are range names! Therefore, you can enter the following formula into Denver's total cell B2:

=Sum(Denver) 

Denver's total computed correctly without range names.

45 Copy and Move Formulas ).

One thing you must keep in mind when using cell labels inside functions and calculations is to enclose the labels in single quote marks if they contain a space. For example, the following two cell entries would produce errors in this sheet:

=Sum(New York)
=Sum(Est 2007)

To correct these entries, you must use quotes like this:

=Sum('New York')
=Sum('Est 2007')

If a label is nothing more than a number, as the year 2005 would be if used as a label in the table, you would not be able to use that label in such calculations, even if you enclosed it inside quotes.

The next figure shows the completed sheet with all the totals. As you can see, all the totals work fine, even though no range names exist.

All totals entered using the table's column and row labels.

[View full size image]

NOTE

Make sure that you use single quotes (') rather than double quotes (") in a label entry, or Calc will not be able to interpret the cell label.