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 are going to introduce references and ranges.
Let's do [a quick review]. We have seen how we can enter a formula into Excel. If I enter a simple formula (=1+1) it displays the value 2, but the actual contents of the cell are a formula.
We have also seen how we can do simple things like sum of 1 and 1 to do the exact same thing. In this case, I am using a built-in function called sum.
Both of these [methods] calculated the same thing, in two different ways. What I am going to show you is how we [can] really unlock the power of Excel.
I am going to enter the number 100 [in cell D2] and the number 42 [in cell D3].
I want [a cell that] adds up [those numbers to] 142 but, [instead] I am going to reference [those] cells.
I am going to add the contents of D2 and D3 (=D2+D3). Notice [that] when I typed D3 now it shows me [by highlighting cell D3]. Everything is color-coded to [illustrate] what I am adding up. [The result is] 142.
I could do the same thing with the [sum] function I could say "what is the sum of D2 and D3" [with the formula =sum(D2,D3)] and it calculates [142] as well.
This is where the magic [of] Excel is unlocked because if I change [cell D2] to 200, both of these [cells] change at the same time. If I now [change cell D3 to be] minus -42 both of the [other cells] change.
I have entered a formula that relies on other cells in the worksheet. The way I do this is by [referencing] the cell. In this [example] I referenced D2 and I referenced D3 and I combine them together in my formula, and we have a calculation that depends on other cells in the worksheet.
[Consider] the scenario where I have my first year grades and I want to calculate my average. I can use the built in average function in Excel (=average) and then I specify which cells I want [to] reference to calculate the average.
I can type in B1 and B2... I think I should show you that you can actually use your mouse as well [to reference cells]. If I [type] comma (,) and then I move my mouse to cell B3, I can reference that cell.
I learned my first year average is 84 (good for me) and now if there is a mark reduction or there is a mark correction: "oh sorry, you didn't get 90 in econ 101... you got 95" Yay! Your average [now automatically] goes up.
This was not the best way [to reference these cells, or] the most efficient way to enter [the formula].
What we can do [instead], is use a new syntax called a range.
I am going to use a colon now, look at the highlighted area saying B1:B5... this is a range of all [of] these cells. When I type [=average(B1:B5)] it calculates the average.
That is an example of a range: a range is always one cell and a colon and another cell.
It can be [must be] a rectangle. For example, we are not averaging grades we are actually averaging a bunch of numbers. I am going to enter a bunch of numbers and I want take the average of all [them]. The way you specify a range is you specify the upper left cell and the bottom right cell. In this [example], the upper left cell would be A1 and then B5 (A1:B5).
You always specify the upper left and the bottom right cell for a range, and [for this example] it will now calculate the average of all [of] those numbers