Excel Tips Weekly

– [Narrator] When you work with formulas in Excel invariably you need to work with cell references. In this work sheet called price increase we’re about to calculate a price increase in cell C 3. Many times when we work with formulas we use what are called relative references. Sometimes we need an absolute reference that’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 formula it 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 okay but 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 data from B 4, B 5 etc, B 6 but we want to continue getting 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 time at the time we put in B 1 or of course coming back now we can make this reference be absolute by clicking behind it in front of it and simply press the function key F 4, that puts in dollars signs, that’s an absolute address.

It’s not necessarily about currency although we’re using dollars and cents here it means if we copied the formula the reference to B 1 does not change. I’ll drag it down a few just to check it and there it is, these are all working properly they’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 here we’re going to close down the warehouse and ship all these items to our other warehouses in different regions.

So, if we were writing a formula for this cell only it’s pretty simple, pretty straight forward. Equal, if the east is going to get 20% of these it’s what, this amount times this amount, that’s pretty straight forward, enter. If you copy this down a few cells just to check it out immediately of course we’re going to realize that something is way off and even if we made this column 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 multiplying by 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 this it’s a little tricky at first but here’s the idea. If we were to write each of these formulas individually and by the way there’s over 400 of them we need here. That’s pretty intimidating but, every formula would involve getting the percentages out of row three. And would involve getting the quantities out of column B. So, if we edit this original formula here.

Let’s say in effect as we focus on the part of the address that has to do with the percent. I think many of you know of course it makes no difference which of these come first but as we focus on the C 3 here I can highlight it or click near it. Let’s press that function key F 4 not just once but multiple times and we see different used of the dollar sign. Whatever follows the dollar sign will not change. So if we change the display to be C dollar sign 3 if we copy this formula else where it will always refer to row three.

The other part of the formula to the right has to do with the quantity that’s always coming out of column B so here too we’ll press F 4 a few times to get the display so that the dollar sign is 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 corner after 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 here take 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 these to the nearest whole number, these are items. Comma zero make that change. And we don’t need to display those, we’ll double click up in the formula bar, decrease the decimals drag this to the right. There we go, and then just double click that lower right hand corner and there are the numbers we would want to work with. Everyone of these is using what’s called a mixed address. A portion of this formula is absolute and a portion is relative. So I’ve seen different uses for needing cell addresses to be absolute and also in this example here mixed.

APT eLearning Translations Source |