Mike has a price sheet in Excel. Column A is Product Name, Column B is Current Price and Column C is the Percentage of Increase for Column B. Bill Sets up Column D to reflect the adjusted New Price and then demonstrates how we can carry the New Prices to Column B as the New Current Price. Follow along with Bill in Episode #1551 to learn how to set this project up and how easy it is to do!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1551 - Add N’ Percent.
Hey, welcome back to MrExcel netcast.
I’m Bill Jelen.
Today’s question by Mike, Mike has a series of prices and someone has gotten through and indicated what the price increase should be for next year.
He needs to calculate what the price increase should be.
Sounds pretty easy to do but some things you need to be careful with and we’ll talk about those.
We start out here with equal and parentheses.
I’m going to do 1+0.05.
So, that’s going to give me like 1.05 and gives me something to multiply times the old price.
Then, we come over here to B2.
Alright.
And we get a price.
Of course, to copy that down to the whole data set, we double click the fill handle and it shoots down.
But, you see, now, we’re having prices like 480.2568.
And while it would be tempting to use decrease decimal to make that look correct, I’m not sure that’s the best way to go because we want to be doing calculations based on this and so on.
So, I’m going to come back up here to the original formula and I’m going to start with =ROUND((1+C2)*B2,2), which will round the number to the nearest 2 decimal places.
Right.
Now, no matter how many decimals we show, it is always just 2 significant digits there.
Now, that we have this, you know, we might want to print this out and send it back to the person who created the increases.
That way we can check it or something like that.
But, at some point, we’re going to want to have this new price replace the price.
Okay.
So, we get to that point.
Now, we’re going to be fairly careful here.
We’re going to copy using CTRL + C, or the copy command and then select the old prices, and instead of doing a paste, we’ll open up and do Paste Values.
Paste Values will copy those formulas into the old slot and then we can get rid of this extra calculation out here.
Alright.
So, it sounds like a simple thing.
Every year we want to increase the prices.
Some high-level manager goes through and marks the percentages.
But, then, we actually have to get that done in Excel combination formula and with the ROUND function, and then, copy, and paste values.
Okay, I want to thank you.
See you next time for another netcast of MrExcel.
Learn Excel from MrExcel podcast episode 1551 - Add N’ Percent.
Hey, welcome back to MrExcel netcast.
I’m Bill Jelen.
Today’s question by Mike, Mike has a series of prices and someone has gotten through and indicated what the price increase should be for next year.
He needs to calculate what the price increase should be.
Sounds pretty easy to do but some things you need to be careful with and we’ll talk about those.
We start out here with equal and parentheses.
I’m going to do 1+0.05.
So, that’s going to give me like 1.05 and gives me something to multiply times the old price.
Then, we come over here to B2.
Alright.
And we get a price.
Of course, to copy that down to the whole data set, we double click the fill handle and it shoots down.
But, you see, now, we’re having prices like 480.2568.
And while it would be tempting to use decrease decimal to make that look correct, I’m not sure that’s the best way to go because we want to be doing calculations based on this and so on.
So, I’m going to come back up here to the original formula and I’m going to start with =ROUND((1+C2)*B2,2), which will round the number to the nearest 2 decimal places.
Right.
Now, no matter how many decimals we show, it is always just 2 significant digits there.
Now, that we have this, you know, we might want to print this out and send it back to the person who created the increases.
That way we can check it or something like that.
But, at some point, we’re going to want to have this new price replace the price.
Okay.
So, we get to that point.
Now, we’re going to be fairly careful here.
We’re going to copy using CTRL + C, or the copy command and then select the old prices, and instead of doing a paste, we’ll open up and do Paste Values.
Paste Values will copy those formulas into the old slot and then we can get rid of this extra calculation out here.
Alright.
So, it sounds like a simple thing.
Every year we want to increase the prices.
Some high-level manager goes through and marks the percentages.
But, then, we actually have to get that done in Excel combination formula and with the ROUND function, and then, copy, and paste values.
Okay, I want to thank you.
See you next time for another netcast of MrExcel.
