Calculating Within Cells
| Within any cell in Quattro Pro, you can store a mathematical formula, and Quattro Pro will display the results of the formula in the cell. The cell can contain numbers or cell references. Using NumbersIn Figure 10.1, I've stored a formula to add a series of numbers in cell A1; if you look at cell A1, you see the result of the formula, but if you look at the Input Line, you see the actual formula. Figure 10.1. Although you store a formula in a cell, Quattro Pro displays the result of the formula. Tip
Notice that the formula in the Input Line begins with a plus sign (+). The plus sign signifies that Quattro Pro is using the numbers that follow in a calculation. Quattro Pro assumes that you want to calculate any values you enter into a cell that are separated by a mathematical operator, as shown in Table 10.1.
A plus sign will precede the formula even if you subtract values; the plus sign simply indicates that Quattro Pro will perform the math indicated by the numbers and mathematical operators that follow. Using Cell ReferencesAlthough you can use raw numbers in a calculation, the power of spreadsheet calculations comes from using cell references in calculations instead of raw numbers. When you use a cell reference instead of a raw number, you can change the contents of cells involved in the calculation and Quattro Pro will update the result of the calculation using the new information. When you examine Figure 10.2, you notice from the Input Line that the calculation in cell B8 adds the contents of the cells in B4, B5, B6, and B7. Although I could have stored +750.50+8007.30+1200.26+500.10 in cell B8, I chose, instead to store +B4+B5+B6+B7. Figure 10.2. Cell B8 contains a calculation that references other cells in the spreadsheet. In Figure 10.3, I changed the value in cell B4 from $750.50 to $1500.00. Because cell B8 contains a calculation that adds the contents of cells B4.B7, Quattro Pro automatically updated the value displayed in cell B8. If I had stored the raw values+750.50+800730+1200.26+500.10in cell B8, Quattro Pro would not have recalculated the value displayed in B8 unless I also changed the calculation stored in B8. Figure 10.3. Because the calculation in B8 refers to cell B4, B8 changes when B4 changes. When you store a calculation that contains cell references, you effectively link the cell storing the calculation to the cells that the calculation references. When you make changes to one of the cells referenced by the calculation, the cell containing the calculation also changes. When you store raw numbers in a cell, Quattro Pro displays the result of the calculation, but the cell containing the calculation in no way depends on any other cells. The cell containing the calculation will change only if you edit the cell and make a change to the calculation. Using cell references when you create mathematical calculations makes it easy for you to ensure that all calculations in a spreadsheet update properly when you change a single number. Understanding the Order of Calculation OperationsBecause Quattro Pro applies the same principles regarding the order of operations when making calculations that you learned in high school, I want to review those standards. Please don't break out into a cold sweat; I promise this won't be hard and it will be useful. If you understand the principles surrounding the order of calculations, you can save yourself some embarrassment by coming up with right answers instead of wrong ones simply because of a misplaced parenthesis. What am I talking about? Read on, please. When you make a calculation, you add, subtract, multiply, and divide two or more numbers. By definition, when you add or multiply numbers, the order of the numbers doesn't matter and the answer will be the same; 2 times 5 and 5 times 2 are both 10. However, when you subtract or divide numbers, the order of the numbers does matter. For example, 10 divided by 2 is 5, but 2 divided by 10 is 1/5 or 0.2 as Quattro Pro or a calculator would show. Okay, that seems simple enough. But, what happens when you mix operations within a calculationthat is, you add, subtract, multiply, and divide all within a single calculation? In these cases, the order in which you perform the operations (as well as the order of the numbers in the case of subtraction and division) will change the results. When modern math was developed, two basic principles were put in place concerning the order of operations within a calculation. The first principle is as follows:
Let's look at a few examples and understand the math behind them. The following calculations all use the same numbers, but the order of the numbers and the mathematical operators changes from one calculation to the next. The answer to calculations 1, 2, and 3 is 6, and the answer to calculation 4 is 13.
Breaking down calculation 1, 4 divided by 2 equals 2. Then, 2 (the result just obtained) times 3 equals 6. Then, 6 (the result just obtained) plus 2 equals 8. Finally, 8 (the result just obtained) minus 2 equals 6. Breaking down calculation 2, 3 times 4 equals 12. Then 12 (the result just obtained) divided by 2 equals 6. Then, 6 (the result just obtained) plus 2 equals 8. Finally, 8 (the result just obtained) minus 2 equals 6. To break down calculation 3, you need to start with multiplication and division, which don't appear at the beginning of the calculation. You start evaluating this calculation with 3 times 4, and then calculation 3 breaks down just like calculation 2. Breaking down calculation 4, you perform multiplication and division first, from left to right. 3 times 4 equals 12; 2 divided by 2 equals 1. Next, perform addition and subtraction from left to right, using the results from multiplication and division. Twelve (the result of multiplying 3 time 4) minus 1 (the result of dividing 2 by 2) equals 11. Finally, 11 (the result just obtained) plus 2 (the last value in the calculation) equals 13. Okay, by now, fond (or not so fond) memories of that high school math should be coming back to you and the cobwebs should be clearing. And I'll bet that you are vaguely (or maybe not so vaguely) remembering something about parentheses affecting the order of calculations. And, indeed, they do. The second principle (remember, I promised only two principles) really adds to the first principle:
Let's re-examine just calculation 4, placing parentheses in the calculation. The answer to this new version of calculation 4 is 5.
This time, when we break down calculation 4, we perform the calculation within the parentheses first: 4 minus 2 equals 2. We then go back to working left to right. Starting at the beginning, 3 times 2 (the result within the parentheses) equals 6. Then 6 divided by 2 equals 3. And finally 3 (the result just obtained) plus 2 equals 5. When you record a formula in a cell, Quattro Pro does math the way I just described. So, the good news here is there's nothing new to learn. |
EAN: 2147483647
Pages: 128