|Baze de date
As you saw earlier in this lesson, when you copy a formula, Excel tries to change the cell references so that they are appropriate to the new location. It does this whether you copy with AutoFill or using more traditional methods such as Copy and Paste.
Most of the time, this is a good thing. However, sometimes you might not want the cell references to change. For example, suppose you put the current interest rate in cell F1, and you want each formula in the worksheet to include it in its calculation. In a case like that, you would want F1 to remain F1 no matter where you copied the formula.
To 'lock' a reference so that it won't change when you copy it to another location, you use an absolute reference by adding a dollar sign ($) before the row and before the column. So, for example, F1 becomes $F$1. If you want to lock only the row, use the dollar sign only before the number, like this: F$1. To lock only the column, place the dollar sign only before the letter, like this: $F1.
Sounds pretty simple, doesn't it? Yet for some reason absolute references are difficult for many people to 'get' conceptually. Therefore, the rest of this lesson is devoted to giving you practice using these references.
If you want to follow along with the examples, enter the text and numbers shown in each figure and then follow the instructions.
example 1: interest rate
Let's start by doing a simple amortization table for a loan using an absolute reference.
Figure 3-3: Use this data for the example.
Figure 3-4 shows the result.
Figure 3-4: The finished example file.
Let's look at some more examples of using absolute references, because understanding them thoroughly is very important. By the time you're finished with this lesson and the assignment, you should understand them thoroughly.
example 2: sale prices
For this example, we look at how some prices are affected by a certain percentage of markdown at a retail store. Suppose you have been directed to create a price list for items that are going to be discounted for a sales event.
Start by reproducing the worksheet shown in Figure 3-5.
Figure 3-5: Start with this worksheet data.
Figure 3-6: The finished example file.
example 3: investment income
If you place a certain sum of money today in a savings account, at a certain interest rate earned, how much will you have in 1 year, assuming interest is compounded weekly? In this final example, you answer that question.
Set up a worksheet that places the beginning balance and the interest rate in separate cells at the top. You'll refer to them in other formulas in the worksheet. Figure 3-7 shows one way to do it, but you'll need to figure out the formulas -- and which values should be absolute references -- on your own.
Figure 3-7: See if you can figure out the formulas to use for this worksheet to produce these results.
Politica de confidentialitate | Termeni si conditii de utilizare