CS 100 (Learn) — CS 100 (Web) — Module 04
NOTE: If your internet access is restricted and you do not have access to YouTube, we have provided alternate video links.
Note: This video transcript has been slightly modified. Corrections are marked with strikethrough, and alternative wording has been placed in [square brackets] to correct some of the awkward or confusing phrasing in the videos.
In this video we're going to motivate and [then] learn how to use absolute referencing.
To build up an example I'm going to create a spreadsheet for a fictitious company called Bob's Widget Palace. At Bob's Widget Palace, they sell widgets they only have one kind of widget it's the best widget you'd ever want.
If you don't know what a widget is, it's a generic term for a product used in a lot of business courses, so it's popular in Excel examples as well... so let's just continue that tradition and say that Bob is selling widgets.
We want a lookup table for the salespeople to know how much to sell the widgets for. To start, a column for quantity and let's just put one [in cell B7] two [in cell B8] and now we can use our autofill feature and let's have up for 10 widgets for sale.
Then we're going to say "how much does it cost to sell [buy] that many widgets?"
It turns out that each widgets costs exactly five dollars so, what I could do is [put in cell C7, =B7*5]. I [also] want to format that as a dollar amount.
If I [autofill from cell C7], it is going to show us a nice little lookup table. One widget is five dollars if you want to buy eight widgets it's $40.
This is a great example of using the autofill feature... but it's not very robust, and what I mean by robust is: it's not very good: what if we decide to change the price of widgets? All of a sudden widgets [are] cheaper and we want to pass the savings on to the customer, so now widgets are four dollars.
What I'm going to have to do is go in and edit [cell C7] and say that it's now =B7*4, and now I'm going to have to copy it all the way down, and that's not going to be very effective if we change our prices frequently.
What I'm going to do is [in cell B4] I enter [the text] "widget price" (and I'll make this cell [column] just a little bit wider so you can see everything) and [in cell C4] I'm going to put five dollars.
Now I am going to say: "Ah ha! The cost of a widget is going to be in [cell C4]". [So in C7], I'm going to [enter the formula =B7*] the widget price [in cell C4]. Fantastic! A widget is five dollars.
Now, when I drag this down [from C7] let's see what happens... Whoa that was unfortunate.
Let's try to find out where we went wrong. Let's take a look at my formula [in C7]. If I go to edit this formula, it's going to highlight the cells that is accessing. It says: "okay, I'm going to take what's to the left (B7) and multiply that [by] what a widget costs in [C4]", so that looks right.
Maybe we [should] look at the next one... that looks incorrect.
Now we see where it went wrong: it is taking the cell from to the left (B8), but now it's trying to get the price of the widget from cell C5... as you can see that highlighted in red.
That is not where the price of the widget is... so what happened was, Excel tried to copy the formula down one cell and [by] doing so it also copies the references in the formula. It makes the [references one larger] as well [because] we copied it down.
If we look down at [cell C16] we can see what is happening. It's using the value of 140 for the price of the widget, so if you can actually see what's going on it's saying 10 widgets cost $1400 that is a crazy price to pay for widgets, so let's fix this problem.
Your intuition might be "well, let's go back to what we did before and just enter the five dollars in the formula directly", but that wasn't very robust.
What I'm going to do is use a special technique... I'm going to start entering this formula again from scratch.
We [still want] the [value] to the left, and I'm going to multiply it by [the cell where the price is]... but what I want to do is tell Excel that I really want that [particular] cell and if you're going to copy, don't change what it's referencing.
The fancy term for this is absolute referencing so I want [cell C4], and when you copy, don't change value of C4... keep that [reference] constant.
The way to do this is press the F4 key. I know that's a little confusing, but if I press the F4 key... look what it did: it added dollar signs in front of the C and in front of the 4 ($C$4).
This is an old convention going back to the early days of spreadsheets and it's done for consistency... it's a little bit of an awkward notation (I'll explain it and motivate it a bit more later) but what this does is say okay I absolutely want C4 so I put dollar signs: $C$4.
You can type that directly... you don't need to use the F4 key, so I could just type in I want =B7*$C$4... it will work just as well, so you can type in those dollar signs yourself but if you know the keyboard shortcut of pressing f4 (at least in Windows) that will save you a lot of trouble.
Now what I'm going to do is copy [cell B7] down and we can see everything is working as we expect. If I look at the bottom cell [C16] it's referencing B16 (the cell to the left) and it's referencing the original C4 as well.
Fantastic... now if we want to change our widget price we can just go and say oh widgets now $4.25... our table updates automatically.
This is our first example of using absolute references... it really helps when you're filling in cells or copying and pasting cells to tell Excel exactly how you want to handle a reference.
Let's take a look at this notation we used again: the dollar sign in front of the C and in front of the 4 here tell it to use that absolute reference of C4.
Let's expand Bob's widget palace... what happens is we have some customers who can get a customer discount. What we want to do now is: based on the customer discount level, we can tell them how much their widgets are going to cost.
What I'm going to do here is I'm going to [in cells D6:G6] add 10% discount, 15% discount, 20% discount and 25% discount (what we'll do is we'll put we'll just make a little bit of space) and [add a label] for "Discount Label").
To calculate how much one widget would cost at a discount level of 10% so [in cell D8] we're going to [enter the formula] as 1 [the quantity from B8] times the widget price [from C4] and I want to make sure I make that absolute reference, so I'm going to put the dollar signs in front, and then I'm going to multiply it by 1 minus the discount [in cell d8] so that's the way I'm going to calculate 10% off. Great now your widget is going to cost $3.825 -- of course we're going to make it a dollar amount and do the rounding automatically for display purposes.
So now let's copy this down... oh that didn't work... we have the same problem we had before, and now it's going to cost four quadrillion dollars for a widget? That does not look good.
We forgot to use absolute referencing [for the discount level] so let's modify cell [D8] and I want to use d7 here... that's the one I want to use and so when I copy it down it's going to now appropriately not change that value. Everything looks a little better now.
Now, let's copy this cell and fill in the entire table so let's go go to here and fill in this whole table and now we've encountered another problem: those numbers do not look right at all.
If you're getting a 25% discount, one widget should not cost $62. What went wrong here? Well, let's take a look at what the original cell is using: its using quantity of one [from B8]... that looks good, discount level of 10% [from $D$7] and widget price of $4.25 [from $C$4]. That looks good.
Let's take a look at cell [E8]... what is it referencing? It's using a discount level of 10% [from D7 and] a quantity of $425 [from C8]. We want the quantity to remain at one [in cell B8] so that didn't work out very well.
When I copied [cell D8] to the right, two things went wrong: first, as I copied it to the right instead of keeping the quantity that we wanted it moved the quantity reference over... so that's not great. [In addition,] the 10% stayed the same so that's not great either.
Let's see if we can fix this. What we're going to do is investigate a little bit more about how those dollar signs really work.
Let's just focus on the quantity first. What I don't want to do is say "hey, I absolutely want quantity 1 [in cell C7]" because that won't work when I carry the cell down... those cells [will] reference the quantity of one.
What I want to do is tell [Excel] that when I copy this cell, I want you to keep the column the same but it's okay to change the row. What I'm going to do here is I'm going to say I want to keep the column the same but don't keep the row the same so in this case I'm only going to put the dollar sign in front of the B [$B8].
When I only put one dollar sign in, it [means]: whatever the dollar sign is in front of, keep that the same. In this case, I keep the column to be B but it's okay to change the number (it's okay to change the row). When I copy the cell down, it's still working correctly.
Now, I can also fix the problem with the discount level. In this case, I want to keep the row the same, but it's okay to use a different column [D$7]. Now when I copy this formula [to the right] it's going to work, and then when I come all the way down... fantastic the entire table is now complete.
If I look at the very bottom right hand corner cell [G17], we can make sure everything aligns properly and everything is being calculated properly... and it looks good: it's using the quantity of 10 (that's what we want) it's using the widget price of $4.25 located at $C$4 and it's using the correct discount level which is located at G$7.
By carefully using the right dollar signs in the right places it will make our life a lot easier when we cut and paste (or we autofill) our cell contents in our Excel spreadsheets.
To recap, a dollar sign means we're using absolute referencing. If you use two dollar signs then it says always use that cell. If we only use one dollar sign then, it matters where the dollar sign goes: if you put the dollar sign in front of the letter that means keep the column the same, if we put the dollar sign in front of the number it means keep the row the same when you're copying this cell, and that is how absolute referencing works.