Relative references
Take a look at the graphic below. Here I'm going to paste the formula from column B into C. Excel uses Relative references as a default... in other words you don't have to specify, it assumes that if you're copying a formula, you want to calculate a different set of data. In this case, sum column C's values. Pretty clever.
However, there are times when I don't want this. There are times when I need a value to stay constant and my formula to calculate against that.
Absolute References
In this example I need to calculate taxes for the toys sold in April. Let's say the rate is 9.25%. Yes, it's a little high, but we are in California and these are toys we're talking about. I could just add a column with the same 9.25 next to each toy, but I decide to place it in cell C2. I do my calcs and then copy the formula down. But something went wrong!
When I copied down, the Relative reference moved down to C3, C4, etc respectively. I did not want that! I want to reference only C2 for all of them. This is where Absolute references come in. All you do is add $ to both the letter and number of the reference. In this case $C$2. See next graphic.
This time it worked. Now the calculations are working as they should. Absolute reference did the trick.
Mixed References
There are times when you need a only the row or the column to stay put while the other changes. To do this you only add 1 $ to the corresponding element you want to stay put. Let's take a look at a simple example:
Here, I selected F5, where the formula is referencing 4x4. $B5*F$1. Without this mixed reference I could never have done this. This allows you to copy/paste formulas with a couple of clicks. Practice it to become better acquainted with this type of reference.
Now, I think I'll some some of that Absolut mixed with some juice sans the pesky relatives.
No comments:
Post a Comment