Absolute Reference Example Video

[One speaker.]

[Screen shows an excel sheet with Title Absolute Reference Example Video.]

Narrator: Welcome, this video demonstrates another example of using an absolute reference in a spreadsheet. In this example I am going to calculate the amount of interest earned at the end of each year, just with a simple interest calculation, there's no compounding of anything sort going on, this is just a simple multiplication problem to calculate the amount of interest.

[Interest Earned = (original amount)*(interest rate)*(number of years).]

Up above, in cell B1, I have the percent that I am earning [5%] and in cell B2 I have the amount of money to invest [$1000.00]. Starting in cell B5, I am going to calculate the amount of interest that would be earned at the end of one year. Assuming that I earn a simple interest of 5% each year.

To do this I type equal [=], I click on the cell for the amount of money, which is B2, but I need to do this as an absolute reference because this amount is not going to change. I can just click on B2 and type in the dollar symbols before the B and before the 2, or I can click on B2 and then type the F4 button [A picture of a keyboard with F4 outlined appears then fades] this will automatically make this an absolute reference. If I type it again it will cycle through different options for absolute references, but in this case we’re just going to do an absolute reference for one particular cell, and that's when you have a dollar sign before the column and before the row.

The next thing I’m going to do is multiply by the percent value in B1. Again I’ll click on B1 and click on F4, and it automatically changes it to an absolute reference. Finally, I am going to multiply by the year because I want to be able to copy this equation down from year to year, the amount of money I have stays the same, the percent I have stays the same but the year changes. So I’m going to click enter.

So if I invested $1000, for one year at 5% interest without any compounding I should earn $50. Now we can copy that equation down to the other years and see what happens. Lets click on B8, so this is what the equation looks like after the fourth year. [= $B$2*$B$1*$A8] So after four years, if I invested $1000 which is cell B2, at 5% interest which is cell B1 for four years which is cell A8, multiplied those together I would get $200 worth of interest, again assuming there isn't any compounding.

Now let's assume I hadn't used the absolute references, instead I am going to make these all relative references in my original equation. The first year still looks right; I still have $50. But if I copied that down to the other numbers, I get all kinds of error messages. Why is that? Because now it's trying to multiply by different cells. For example in year three, it's trying to multiply by the header that says interest earned times an empty cell times the number three and obviously Excel doesn't know how to do that.

But once again if I want to go back and make these absolute references, I can highlight the reference, use the button F4, hit “enter” and then copy that equation down again. And again the correct equations come out and now it's referencing the proper cells for our equation. And once again why would we do this, well what if we wanted to see if I changed the amount of money I put in to $1500, notice all of my equations automatically changed. Or all of my amounts of interest earned automatically changed without me having to go back and edit each cell individually.

I can also change the interest rate. Lets say 3% instead again it automatically edited all of the interest earned equations. This is just one example of the many ways you can use absolute references in your equations in Excel.

[End of video.]