Absolute vs. Relative References in Excel L Mancini English (auto-generated) English

Absolute vs. Relative References in ExcelYouTube↗ IA↗(long shot)
Education 719 views 0 likes 1 dislikes 23 Jan 2015

L Mancini 724 subscribers United States YouTube↗

Click to Play Video

so today I'd like to speak with you a little bit about absolute versus

Today I'd like to speak with you a little bit about absolute versus

relative references have you ever noticed that when you autofill down

relative references. Have you ever noticed that when you autofill down

through a column or autofill across the formulas sometimes appear to change I'll

through a column or autofill across, the formulas sometimes appear to change? I'll

give you an example here right now in cell b5 I've got the formula equals a5

give you an example here. Right now in cell B5, I've got the formula =A5. When

when I autofill down through the formula does not look exactly the same when in

I autofill down through, the formula does not look exactly the same in the

the other cells in cell b6 the formula appears to be equals a 6 in cell b7 the

other cells. In cell B6, the formula appears to be =A6. In cell B7,

foremost equals a 7 how is it that Excel decides to change which cell is being

the formula is =A7. How is it that Excel decides to change which cell

referred to and how does it know what new cells to refer to before I get into

is being referred to, and how does it know what new cells to refer to? Before I

that I'd like to talk a little bit about some examples that you might be a little

get into that. I'd like to talk a little bit about some examples that you might

bit more comfortable with with the English language first imagine if you will that were in

be a little bit more comfortable with. with the English language. First, imagine

the classroom there are several students

if you will, that we're in a classroom. There are several students in the

in the classroom and I call out individuals names if you knew the names

classroom, and I call out individuals, names. If you knew the names of all the

of all the students in the class you would know precisely to whom I was

students in the class, you would know precisely to whom I was referring.

referring suppose instead however I stood beside a row of students and I

Suppose instead, however, I stood beside a row of students, and I said, "the person to

said the person to my left I then moved to be to the right of another student

my left." I then moved to be to the right of another student and again said, "the

and again said the person to my left and I repeated that process I moved a few

person to my left," and I repeated that process. I moved a few steps; I was beside

steps I was beside another student and again I said the person to my left every time I'm saying the person to my

another student to my left, and again I said, "the person to my left. Every time I'm saying,

left I'm referring to a different individual in order to know of whom I am

"the person to my left," I'm referring to a different individual. In order to know

referring or to whom I'm referring you need to know not only what it is I am

to whom I'm referring, you need to know not only what

saying but also where I am relative to

it is I am saying, but also where I am relative to the other students in the

the other students in the class so you'll know exactly who is to my left people's names are like an absolute

class, so you'll know exactly who is to my left. People's names are like an

referent with an absolute reference it does not matter what cell you happen to

absolute reference; with an absolute reference, it does not matter what cell

be in you refer to a particular cell and

you happen to be in -- are you referring to a particular cell and you know exactly

you know exactly what cell is being referred to with a relative reference in

what cell is being referred to. With a relative reference, in order to be able

order to be able to understand what cell is being referred to you need to

to understand what cell is being referred to, you need to understand

understand not only what was the person typed in into the cell but also in which cell you

not only what the person typed in into the cell, but also in which cell you

happen to be another example suppose you were to receive an old-fashioned

happen to be. Another example: Suppose you were to receive an old-fashioned

postcard from somebody and they said guess what I'm such and such a date and

postcard from somebody, and they said, "Guess what? On such-and-such a date -- and

they specified month/day/year I had a wonderful time and this is what I did

they specified the month, day, and year -- I had a wonderful time and this is what I did..."

you would know precisely what day was so wonderful for them but then instead of

You would know precisely what day was so wonderful for them; but then instead of

saying a particular date suppose they said yesterday I'd had a wonderful time

saying a particular date, suppose they said, "Yesterday, I had a wonderful time..."

just by seeing yesterday by self you

Just by seeing "yesterday" by itself, you don't know what day was so wonderful for

don't know what day was so wonderful for them in order to figure out which day was so wonderful for them you also need

them. In order to figure out which day was so wonderful for them, you also need

to know what day the person wrote the postcard this is another example of an

to know what day the person wrote the postcard. This is another example of an

analogy the specific date would be like an absolute reference the relative or

analogy: The specific date would be like an absolute reference;

the yesterday would be like a relative reference you're probably familiar with

"yesterday" would be like a relative reference. You're probably familiar with

relative references already a relative reference to cell a5 would just be a 5 so although you and I see the contents of cell b5 is being the formula equals a

relative references already. A relative reference to cell A5 would just be "A5". So although you and I see the contents of cell B5 as being the formula

5 Excel doesn't see it that way a 5 to excel is a relative reference to what

=A5, Excel doesn't see it that way. A5, to Excel, is a relative reference,

Excel really sees how Excel interprets what was typed in is equals the contents

so what Excel really sees -- how Excel interprets what was typed in -- is "equals

of the cell directly to the left of this one and as I auto-filled through Excel from its perspective copied the formula completely faithfully so in cell b6 it

the contents of the cell directly to the left of this one," and as I auto-filled through, Excel -- from its perspective -- copied the formula completely faithfully. So in cell B6 it also sees "equals the contents of the cell directly to the

also sees equals the contents of the

cell directly to the left of this one but now the cell directly to the left of

left of this one," but now the cell directly to the left of this cell is no

this cell is no longer a5 the cell directly to the left of b6 is a 6

longer A5; the cell directly to the left of B6 is A6. Similarly with these

similarly with these other items suppose I wished to autofill through and I

other items. Suppose I wish to autofill through, and I really wish to keep

really wished to keep referring to cell a5 I no matter which cell I'm in I

referring to cell A5. No matter which cell I'm in, I really wish to

really wish to refer just to a 5 I could do that by pressing the a and the 5 with

refer just to A5. I could do that by prefacing the "A" and the "5" with dollar

dollar signs equals dollar sign a dollar sign five as you can see up here

signs: =$A$5, as you can see up here.

now when I autofill through Excel continues to refer to cell a5 in the new

Now when I autofill through, Excel continues to refer to cell A5 in the

cells well why would I sometimes wish to refer to a cell in a relative manner and

new cells. Well, why would I sometimes wish to refer to a cell in a relative

other times wish to refer to it absolutely if you plan to type in a

manner and other times which to refer to it absolutely? If you plan to type in a

formula into a cell and you never plan to copy that formula you never plan to

formula into a cell and you never plan to copy that formula, you never plan to

move it you never plan to autofill it's just gonna stay in that cell it does not

move it, you never plan to autofill, it's just going to stay in that cell, it does

matter whether you're using an absolute

not matter whether you're using an absolute or relative reference. I

or relative reference I personally would prefer to use a relative reference in

personally would prefer to use a relative reference in that context just

that context just because it seems a little bit less cluttered than having

because it seems a little bit less cluttered than having those extra two

those extra two dollar signs in there for each reference when this matters is

dollar signs in there for each reference. When this matters is when you *do* plan to

when you do plan to copy move autofill the contents of a cell into another cell I have here a very simple grade book for a hypothetical class and I've got the

copy, move, or autofill the contents of a cell into another cell. I have here a very simple gradebook for a hypothetical class and I've got the exam averages, the

exam averages the homework average is a professional at fridge professionalism

homework average, a professionalism average and I've

average and I've got a course grade I've done some analysis on the grade using

got a course grade. I've done some analysis on the grade using different

different functions the average function the max function and the min function

functions: the average function, the max function, and the min function, and based

and based on these grades I thought it appropriate to give a 3-point curve to

on these grades I thought it appropriate to give a three-point curve to the class.

the class so you can see here I've hard coded the curve as being three points

So you can see here I've hard-coded the curve as being three points. Now as you

now as you know you're not supposed to hard-code date in more than one time

know, you're not supposed to hard code data more than one time: you're not

you're not supposed to type in a particular number with a particular

supposed to type in a particular number with a particular meaning more than one

meaning more than one time in excel so in order to come up with our curved course grade I'm gonna add equals the course grade equals III plus the curve

time in Excel. So in order to come up with our curved course grade, I'm going to add equals, the course grade: =E3, plus the curve, which equals B10 (=E3+B10) and I accept

equals b10 and I accept the results

anytime you type in any sort of formula always run the results past a common

the results. Anytime you type in any sort of formula, always run the results passed

sense test does this make sense 78 as a curve course grade if the

a common-sense test: Does this make sense? 78 as a curved course grade if the

original grade is 75 and the curve is 375 plus 378 yes that makes sense

original grade is 75 and the curve is 3? 75 + 3=78; yes, that makes sense. So let's

so let's autofill down through hee and let's take a look at the results

autofill down through here and let's take a look at the results.

anytime you autofill a formula into other cells again run it through the

Anytime you autofill a formula into other cells, again run it through the

common sense test do these results pass the common-sense test no they do not if

common sense test. Do these results pass the common sense test? No, they do not. If

you have a situation like this where the results look good in your first cell you

you have a situation like this, where the results look good in your first cell, you

auto fill copy move the results into other cells and you get garbage into

autofill/copy/move the results into other cells and you get garbage in

some or all of the other cells chances are you probably have a relative

some or all the other cells, chances are you probably have a relative reference

reference where you meant to have an absolute reference or vice versa or you

where you meant to have an absolute reference or vice versa, or you might

might have multiple references wrong when you're in a situation like that

have multiple references wrong. When you're in a situation like that, what you

what you should do is you should just click on one of the cells that contains

should do is, you should just click on one of the cells that contains a

data that is obviously incorrect result that is obviously incorrect and look at

result that is obviously incorrect, and look at the

the formula see if it refers to the cells that you wanted to refer to equals e 5 e 5 is that what we want that's Linus that helps curve course grade on

formula. See if it refers to the cells that you wanted to refer to. =E5. E5 -- is that what we want? That's Linus Van Pelt's uncurved course

curve course grade yes that's exactly what we want so that part's good plus b12 we don't want to refer to b12 b12 is

grade. Yes, that's exactly what we want, so that part's good. +B12. We don't want

the highest grade we want to refer to

to refer to B12. B12 is the highest grade; we want to refer to the curve, and

the curve and we want to keep referring to the curve as we autofill down through

we want to keep referring to the curve as we autofill down through, so what we

so what we need to do is we need to go back to the cell that contain the

need to do is, we need to go back to the cell that contain the original formula

original formula and change this relative reference into an absolute

and change this relative reference into an absolute reference. We're prefacing the

reference by referencing the B with a tent with a dollar sign and preface attend with a dollar sign now we can

B with a dollar sign and prefacing the 10 with a dollar sign. Now

autofill through and the results we see do make sense so you can see in this

we can autofill through, and the results we see do make sense. So you can see in

particular formula we need to have one relative reference and one absolute

this particular formula we need to have one relative reference and one absolute

reference incidentally there's a third type of reference called mixed reference

reference. Incidentally, there's a third type of reference called a "mixed reference"

and that has only one dollar sign relative references have no dollar signs

and that has only one dollar sign. Relative references have no dollar signs,

absolute references have two dollar signs mixed references have one so you

absolute references have two dollar signs, mixed references have one. So you

could have as a mixed reference either down a sign b10 or B dollar sign 10 the

could have as a mixed reference either $B10 or B$10.

dollar sign indicates that that particular dimension should be taken

The dollar sign indicates that that particular dimension should be taken in

in an absolute fashion the lack of a

an absolute fashion; the lack of a dollar

dollar sign indicates that it should be a relative reference so if I'd had

sign indicates that it should be a relative reference. So if I'd had

dollar sign be ten you mean keep absolute with regards to the column I

$B10, it would mean keep absolute with regards to the column. I still want

still want to keep B even if I'm auto filling across if I didn't have a dollar

to keep "B" even if I'm auto filling across. If I didn't have a dollar sign

sign before the ten it would mean if I'm going to be Auto filling down B relative

before the ten it, would mean that if I'm going to be auto filling down, be

with regard to what row you're referring to so again why did we need to have an

relative with regard to what row you're referring to. Again, why did we need to

absolute reference for b10 in cell f3 with the relative reference for b10 what

have an absolute reference for B10 in cell F3? With the relative reference for

Excel really saw was retrieve the

B10, what Excel really saw was "retrieve the contents of the cell one,

contents of the cell one two three four columns to the left and one two three four five six seven

two, three, four columns to the left and one, two, three, four, five, six, seven rows

rows down so four columns left seven rows down in cell f4 four columns left

down, so four columns left seven rows down. In cell F4, four columns left seven

seven rows down would no longer refer to be ten it would now refer to be eleven so what have we learned today there are three different types of references a

rows down would no longer refer to B10; it would now refer to B11. So what have we learned today? There are three different types of references: a relative

relative reference which has no dollar sign a mixed reference with one dollar

reference which has no dollar sign, a mixed reference with one dollar sign, and an

sign an absolute reference with two dollar signs if you are not going to be

absolute reference with two dollar signs. If you are not going to be using the

using a formula in any other cells it does not matter what type of reference

formula in any other cells, it does not matter what type of reference you use. If

you use if you do plan to copy autofill or move the contents of one cell into

you do plan to copy, autofill, or move the contents of one cell into another cell,

another cell you do need to be careful about what type of reference you use if

you do need to be careful about what type of reference you use. If you wish to

you wish to refer to the exact same cell when your auto filling or copying or

refer to the exact same cell when you're autofilling or copying or moving the

moving the contents of a formula you need to use an absolute reference if you'd like to keep the same relationship

contents of a formula, you need to use an absolute reference. If you'd like to keep

between cells so as I Auto filled from here to here I no longer wish to refer

the same relationship between cells so as I auto-filled from here to here I no

to Charles Brown course grade I now wish

longer wish to refer to Charles Brown's course grade, I now wish to refer to

to refer to sally brown's of course grade I'm preserving the relationship of

Sally Brown's course grade -- I'm preserving the relationship of the cells

the cells by using a row of reference in each case I wish to

by using a relative reference. In each case, I wish to refer to the

refer to the contents of the cell to the left of the cell in which I'm typing in

contents of the cell to the left of the cell in which I'm typing in my formula

my formula or Auto filling through my formula mixed references are going to be

or auto filling through my formula. Mixed references are going to be absolute in

absolute in one dimension relative in another dimension you don't see mixed

one dimension, relative in another dimension. You don't see mixed references

references as often as you do absolute or relative references I hope you found

as often as you do absolute or relative references. I hope you found this video

this video and thank you very much

educational. Thank you very much.

Settings Privacy About Contact Donate