Entering Values
Calc is designed to do calculations. To do calculations, you need entries in your spreadsheet to provide the raw data to be used in the calculations. And to actually do a calculation, you need a formula that will take the raw data and return some sort of answer. Numerical entries and formulas in a Calc spreadsheet are referred to as values. The cells that hold them have numerical significance, which is just the opposite of the text entries that we talked about in the last section.
Numeric values in the spreadsheet can be entered using the 1 through 0 numeric keys. You are not required to enter commas, dollar signs, or percent signs in a cell (this can all be taken care of using different formatting options). You are required to place the decimal point in the correct place, however.
To enter a value in a cell, click on that cell using the mouse (we will talk about using the keyboard to navigate your spreadsheet later in this chapter). Enter the number and then click the Accept button, press the Enter key, or press one of the arrow keys on the keyboard.
The numeric value will be entered in the cell. When you enter values such as numbers into your spreadsheet, they will be aligned on the right of the cell. Make sure that you check any values that you enter into your cells. Typing mistakes make up most of the errors typically found in spreadsheets.
To Understand Formulas and Functions
When you enter formulas into your cells, you have two options: you can build a formula yourself using the appropriate operators, or you can use one of Calc's built-in formulas called functions. Calc treats formulas and functions as values in the spreadsheet. If you think back to Chapter 1, the Value highlighting feature would highlight any numeric values, formulas, or functions that were present in the spreadsheet (it ignored text entries).
Building your own formula requires that you set the formula up from scratch, supplying the correct operators, and specifying the appropriate cells to be acted upon. Chapter 5 provides details on building your own formulas in the section “Creating Formulas.”
Functions do most of the work for you; when you use a built-in function, all you are really required to do is understand what the function is designed for and then designate the cells you wish it to act upon. You will find that there are statistical functions, financial functions, and trigonometric functions, plus functions for just about every possible type of calculation you would like to do (there are more functions than you can shake a stick at). Chapter 5, “Working with Formulas and Functions,” provides a great deal of coverage on Calc functions.
To Enter a Formula
As already mentioned, a formula is an expression or equation that you design to perform calculations. You need to supply the appropriate operators and then specify the cells to be acted upon. In short, you must design your formula to mathematically produce the intended answer.
When you build a formula in a cell, you need to start the formula with the equals sign (=). This lets Calc know that a formula is being placed in the cell. You then follow the equals sign with the appropriate cell references and the correct operator. For example, to multiply cell D7 by cell E7, your formula would look like this: =d7*e7. Note that the asterisk (*) serves as the multiplication sign.
Note
You can enter the equals sign to begin a new formula by pressing the equals sign key or by clicking the Function button on the Formula toolbar.
Once you press the Enter key, click the Accept button, or advance to another cell in the spreadsheet, the result of the formula will appear in the cell. You can view the formula and its results at the same time by selecting the cell. The results will appear in the cell and the formula will appear on the Formula bar's Input line. Figure 2.2 shows the results of a formula in cell F7 and the formula itself on the Input line.
Figure 2.2. The formula's results appear in the cell; the formula itself appears on the Input line.
Note
A good rule to follow is to create your own formulas only when Calc doesn't have a Function that will do the same job. This means you can limit most of your formula writing to simple math like subtraction, multiplication, and division.
To Enter a Function
Functions are built-in formulas provided by Calc. As long as you select the appropriate Function (for your purpose) and then specify the correct cells to be acted upon, you should get the correct answer. Chapter 6, “Working with Functions,” provides a great deal of coverage on Calc functions.
You will find that there is a broad range of Calc functions available: everything from a simple mathematical function called SUM (it can add a column of numbers for you) to financial functions such as the PMT function, which can help you calculate your monthly house or car payment.
The most straightforward way to enter a function into a spreadsheet is by using the AutoPilot Functions button on the Formula toolbar. When you click the button, the AutoPilot Functions dialog box appears (see Figure 2.3). This dialog box provides a complete listing of all the Calc functions.
Figure 2.3. The AutoPilot provides access to Calc functions.
You can view all functions by selecting All on the Category drop-down list (this is the default), or you can view functions in categories, such as Database, Date and Time, Logical, Mathematical, etc. When you find the function you wish to use, you select it from the list and then click Next. The AutoPilot Functions dialog box will walk you through the process of entering the cell references of the cells that you need to include in the function.
Again, we will be spending a great deal of time working with the AutoPilot in Chapter 6. Before we leave this basic introduction to functions, however, we should actually enter a function into a spreadsheet.
Probably the most commonly used function is SUM. You can use it to add two cells together or to add an entire row or column of numbers together. SUM takes on the basic format of a Calc function: =sum(range of cells), where the function begins with the equals sign followed by the name of the function. The cells that are to be acted upon are listed in parentheses following the function's name.
Note
Not all the functions provided by Calc will follow the simple format embraced by the SUM function. Some functions will list their cell targets differently and sometimes include additional parameters.
Using the SUM function is really straightforward. Click in the cell where you want to place the function. For example, let's say that you want to add a column of numbers (see Figure 2.4).
Figure 2.4. SUM can be used to quickly add a column of numbers.
To Use the SUM Function
1. |
Click to select the cell where you wish to place the SUM function. In this case, we've selected F12, so that we can add the column of numbers in column F (the blank cell between F12 and the bottom of the column of numbers is there for aesthetics; it will not affect the result of the function). |
2. |
Click the Sum button on the Formula toolbar. |
3. |
The SUM function will appear in the cell and the cells to be added together will be automatically selected by the function (cells F7 to F11 will be selected by a blue box as shown in Figure 2.5). Figure 2.5. The SUM function selects the cells that it thinks you want to add.
|
4. |
If the correct cells have been selected by the function, press the Enter key to place the function in the cell. If the cell references are incorrect, you can use the mouse to select the cells that should appear in the function. Then press the Enter key to complete the function. |
As with the formula that we looked at earlier, the result of the function will appear in the cell and the function itself will appear on the Input line (when the cell is selected).
To Enter Dates
Before we leave this discussion of entering values into a spreadsheet, we should discuss entering dates and times into a spreadsheet. Calc actually treats dates and times as values. For example, when you enter a date such as 08/09/01 (August 9, 2001), Calc formats the information as a date, but actually sees the date as the number of days that have elapsed since January 1, 1900.
Note
If you wish to view a date as the elapsed time since January 1, 1900, select the cell that holds the date, then select Format, Cells. In the Cell Attributes dialog box, select Number in the Category box and then select OK. The date will now be formatted in the spreadsheet as a number. For example 8/9/00 has the actual numerical value of 36,747. How time flies.
One more thing: If you enter a date as text, such as August 9, 2001, Calc will actually convert the information to the default date formatting (the date will appear as 8/9/01). So, even when you enter text into a cell (the name of the month), Calc will recognize it as a date and convert the information into a date value.
In terms of time, time is actually computed as a percentage of 24 hours. To you, 10:45 AM may be time for a coffee break, while for Calc, 10:45 AM is actually the decimal value of .4479. In your Calc spreadsheets, however, Calc displays the time as it would appear on your digital watch. In fact, the default format for time entries in your spreadsheets is the 24-hour international format.