Updating Calculations

As you change values and formulas in your spreadsheet, you want the contents of the entire spreadsheet to update accordingly . You can set up Calc to update the spreadsheet automatically as you make each change, or you can update the spreadsheet manually.

One reason you might want to update a spreadsheet manually is if it's large and has a lot of linked elements, like charts and linked content from other spreadsheets. If it takes a second or two for the linked elements to update each time you make a change in the spreadsheet, that can get frustrating. Manually updating the spreadsheet lets you make all your changes and then update all at once.

Updating Automatically

1. Update links to data in other sheets or Calc files (if applicable ). See Updating Linked Sheets on page 522.

2. Choose Tools > Cell Contents.

3. If AutoCalculate doesn't have a check mark next to it, choose AutoCalculate. (A check mark means it's activated.)

Updating Manually (F9)

1. Update links to data in other sheets or Calc files (if applicable). See Updating Linked Sheets on page 522.

2. Choose Tools > Cell Contents.

3. If AutoCalculate has a check mark next to it, choose AutoCalculate to remove the check mark.

4. When you want to update the spreadsheet, press the F9 key.

Double-Checking Totals

Because calculations can get tricky, especially if you're using cell references in other sheets and other Calc files, it's good practice to double-check results.

Double-checking a total is simply a matter of calculating the total a different way, as Figure 20-13 illustrates.

Figure 20-13. Double-checking totals

You can also double-check totals in conjunction with conditional formatting to visually set off totals that don't match (see Conditional Formatting on page 578). You can also use the IF function (next) to generate text if your totals don't match. For example, "Your totals don't match. You're in big trouble. Call your attorney."

IF Function

The IF ("if") function is one of the most useful functions. It's described here to not only help you use it, but to illustrate the possibilities that functions present.

The logic that Calc uses in the IF function is the same logic you use in language all the time. For example: "If you put that wagon wheel coffee table in the living room, I'm leaving; otherwise, I'll stay." In other words, if something is true, something specific will happen. Otherwise , something else will happen.

Here's how you would give Calc your wagon wheel coffee table (WWCT) ultimatum:

=IF(WWCT in living room;"I'll go";"Otherwise I'll stay")

(You really don't need to tell Calc this. Calc is not notorious for putting wagon wheel coffee tables in people's living rooms, unlike other spreadsheet programs.)

So let's get real. In your spreadsheets, you'll use the IF function when the value of a specific cell will fluctuate. For example, if you're keeping track of a budget, and you want a visual cue to tell you when the total amount you've actually spent is over your budget, you can have a cell display certain text to indicate when you're still within your budget and display different text when you're over budget, as shown in Figure 20-14.

Figure 20-14. Using IF to berate oneself for going over budget

Notice that each section inside the IF function is separated by a semi- colon . This differs from Excel, which uses commas. Put text within double quotes.

A more complex use of the IF function involves using cell references as conditions, as the following example illustrates.

Let's say a salesperson makes 5 percent commission on sales up to $10,000, and 8 percent on the amount of sales over $10,000.

You could set up an IF function that calculates the total amount of commission, taking into account a higher percentage on sales over $10,000, as shown in Figure 20-15. The math in this kind of formula can get really tricky, so be sure you check your work.

Figure 20-15. IF function used for calculating total commission earned

The spreadsheet in Figure 20-15 is set up to allow flexibility. Instead of entering the numbers $10,000, 5%, and 8% directly into the formula, they are entered into cells. The formula then references those cells, which means you can change the values in the cells without changing the formula. For example, if the Target amount changed from $10,000 to $8,000, or if the commission percentages changed, you can change those values in the cells , and the commission amount is adjusted automatically.

You can also use the IF formula in conjunction with conditional formatting to have the cell contents display in different styles when conditions change. See Conditional Formatting on page 578.