– [Narrator] When you work with formulas in Excelinvariably you need to work with cell references.In this work sheet called price increasewe’re about to calculate a price increasein cell C 3.Many times when we work with formulaswe use what are called relative references.Sometimes we need an absolute referencethat’s going to be the case here.A formula for the new price can be,and there are some variations on it.No matter how we write the formulait will involve these two cells.Equal the current amount times,the percent of increase.
And we’ll add that increase onto the existing amount.So there’s the answer there.And by the way, probably should round that too.We don’t need any pennies in this.Comma two means round to the nearest two decimal places.There we go, so that’s an exact amount there.Now the problem with this formula if we copy downward is,that the references to B 3 will properly adjust, that’s okaybut the reference to B 1 will adjust as well.
This is best showing just by example.As we drag downward we do want to pick up datafrom B 4, B 5 etc, B 6 but we want to continuegetting data out of B 1.That’s not what’s happening.The reference to B 1 needs to be absolute.So we go back to our original formula, double click.And of course when you do know this you do it at the timeat the time we put in B 1 or of course coming back nowwe can make this reference be absolute by clickingbehind it in front of it and simply press the function keyF 4, that puts in dollars signs, that’s anabsolute address.
It’s not necessarily about currency althoughwe’re using dollars and cents hereit means if we copied the formula thereference to B 1 does not change.I’ll drag it down a few just to check itand there it is, these are all working properlythey’re all referring to cell B 1.Double click, copy down the column, we’re all set.That’s the use of an absolute address.The next worksheet over to the right, Mixed References,has a different kind of need.We’ve got some items in a warehouse herewe’re going to close down the warehouseand ship all these items to our other warehousesin different regions.
So, if we were writing a formula for this cell onlyit’s pretty simple, pretty straight forward.Equal, if the east is going to get 20% of theseit’s what, this amount timesthis amount, that’s pretty straight forward, enter.If you copy this down a few cells just to check it outimmediately of course we’re going to realize thatsomething is way off and even if we made thiscolumn a lot wider, of course we’ve got big problems here.So what’s going on?Similar to what we saw before, now we’re multiplyingby those two cells.
Obviously we need some changes.So I’m going to press control Z a few times, get rid of those.In thinking this out if you’ve never encountered thisit’s a little tricky at first but here’s the idea.If we were to write each of these formulas individuallyand by the way there’s over 400 of them we need here.That’s pretty intimidating but, every formulawould involve getting the percentages out of row three.And would involve getting the quantitiesout of column B.So, if we edit this original formula here.
Let’s say in effect as we focus on thepart of the address that has to do with the percent.I think many of you know of courseit makes no difference which of these come firstbut as we focus on the C 3 hereI can highlight it or click near it.Let’s press that function key F 4 not just oncebut multiple times and we see different usedof the dollar sign.Whatever follows the dollar sign will not change.So if we change the display to be C dollar sign 3if we copy this formula else whereit will always refer to row three.
The other part of the formula to the righthas to do with the quantity that’s always comingout of column B so here too we’ll press F 4 a few timesto get the display so that the dollar signis in front of the B in front of the 4.Of course you could’ve typed these as well too.So we’re all set here, we’ll press control enter,give this a shot, drag it downward, what’s happening?Looks pretty good as we adjust the column width up there.Looking good, let’s drag this to the right,how’s that looking?That’s looking pretty good too.
We can get rid of these for the moment,double click that lower right hand cornerafter highlighting these cells.Double click,so where it’s all the way down the column.We’re using absolute addresses.And one more change we want to make heretake a look at this bamboo chair here.Can we have .4 of a chair, half a chair?Nope, we’d want to use rounding in all of these.Let me backtrack for a moment, press control Z.I’m going to change this formula right here.We’ll copy to the right and downward by doing rounding.
And a simple round here we want to round theseto the nearest whole number, these are items.Comma zero make that change.And we don’t need to display those, we’ll double clickup in the formula bar, decrease the decimalsdrag this to the right.There we go, and then just double clickthat lower right hand corner and there are the numberswe would want to work with.Everyone of these is using what’s called amixed address.A portion of this formula is absoluteand a portion is relative.So I’ve seen different uses for needingcell addresses to be absoluteand also in this example here mixed.