We saw in a previous session on Creating Basic Formulas - that when you copy the formula to another cell the copied formula automatically changes relative to where the copy started. For example if in cell D5 the formula is =C5-B5; and you copy that formula to cell D6 the formula in D6 will be =C6-B6. That is the default in Excel and that is called relative addressing - when a formula is copied change the address for the copied cell relative to where the copy started.
However, sometimes you don't want that to happen - and that is when you have to understand Absolute Addressing. That's what this topic is all about comparing Absolute versus Relative Addressing. The topics are:
For this example I am using the sheet named Pricing from our sample file. In this scenario there is a list of products and their regular price. The prices are going to be discounted by the percentage amount that is in F1. Here is the worksheet.
We are going to enter a formula is cell C4 that will calculate the amount of the discount for the first product. This formula is going to have an error and to understand the error I will explain Absolute versus Relative addressing.
Let's take a look at the formuals in column C.
So our problem is that:
Here is the solution - we are going to re-enter the formula making cell F1 absolute.
For this example I am using the Percent tab in the sample file. This sheet shows a list of employees and their salaries for a period of time. I want to calculate the percentage of each person's salary compared to the total salary. Here is the worksheet.
Here are the steps:
Now that you have completed this session and viewed the video give it a try. On the sample file click the Exercise1 worksheet. Use Absolute Addressing to calculate the cost of Black & White copies for each department and then do the same thing for the cost of the Color copies. When finished you can check out the answer by clicking the Exercise1Results worksheet.Good luck - I bet you do great!!
