Another dual podcast as Bill Jelen and Mike Gel Girvin show you multiple ways to reduce a range by 35%. Shot in Seattle, Washington. Episode 966 shows you how.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, we start out with massive amount of data.
How we're gonna analyze as well.
Let's fire up a pivot table.
See if we can solve this problem.
Hey, alright! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
I'm still here in Seattle at Highline Community College.
Mike Gel Girvin.
Now, Mike got this question on YouTube this morning.
He said someone has a whole column of prices here and they want to reduce all of those by 35%.
And so, we're gonna play dueling Excel again my way and his way and see which way we want to go.
So, what I want to do is, I'm gonna put a little formula here that basically I want to get 65 65% in there and so now 1 plus, here we go, 65%, so we're good.
I'm going to copy that and change it to values.
So of course, the fast way to go.
Just go to the right edge of track right, drag left.
That was with the right mouse click, copy here, is value only.
Now, I'm going to copy that.
I'm going to come over and choose my range of prices.
So, [ ctrl + shift + down arrow ], to choose that and then I use [ Alt + E + S + M ].
The multiply operation.
Now, one thing I want to do here is I...
Then want to go up and hit values because I don't want the formats coming right.
I want the green being pasted over.
I don't want the percentage being pasted over.
So, multiply and values and I click let's just take a quick look here.
We were at 6619.
I click [ ok ] and we're down to 43.02 which seems like about 35% reduction.
Now, that's the one thing with this, you know, you have no audit trail to go back and check it.
But order of magnitude it sounds about right.
So, that's my method.
Now, I'm going to undo, undo to get those original numbers back.
I'm on keep on doing and what Mike shows us his method.
Mike: Alright! Now that was a good one, not bringing that format over with the paste special values.
Another way to do this is, I would do the same thing equals 1 plus That minus 35%, and then I just create a new column.
New price.
I'm going to click here and come up to the home Ribbon and grab that little thing and click right there, and I'm going to do a simple formula equals one cell to my left times this 65%.
Now, I need to lock this and since I'm going down across the numbers, the rows.
I'm going to hit [ F4 ] twice, so the dollar signs adjust in front of the number.
[ ctrl + enter ] to keep that cell highlighted then double click and send the fill handle down.
Hey! Did that work?
Six it did.
Look at that the other trail right here, we started with 10, it goes to 6.50.
MrExcel: That's a good one.
Alright! Sweet there you have it.
Two different ways to solve that problem. Oh, hey!
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amount of data.
How we're gonna analyze as well.
Let's fire up a pivot table.
See if we can solve this problem.
Hey, alright! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
I'm still here in Seattle at Highline Community College.
Mike Gel Girvin.
Now, Mike got this question on YouTube this morning.
He said someone has a whole column of prices here and they want to reduce all of those by 35%.
And so, we're gonna play dueling Excel again my way and his way and see which way we want to go.
So, what I want to do is, I'm gonna put a little formula here that basically I want to get 65 65% in there and so now 1 plus, here we go, 65%, so we're good.
I'm going to copy that and change it to values.
So of course, the fast way to go.
Just go to the right edge of track right, drag left.
That was with the right mouse click, copy here, is value only.
Now, I'm going to copy that.
I'm going to come over and choose my range of prices.
So, [ ctrl + shift + down arrow ], to choose that and then I use [ Alt + E + S + M ].
The multiply operation.
Now, one thing I want to do here is I...
Then want to go up and hit values because I don't want the formats coming right.
I want the green being pasted over.
I don't want the percentage being pasted over.
So, multiply and values and I click let's just take a quick look here.
We were at 6619.
I click [ ok ] and we're down to 43.02 which seems like about 35% reduction.
Now, that's the one thing with this, you know, you have no audit trail to go back and check it.
But order of magnitude it sounds about right.
So, that's my method.
Now, I'm going to undo, undo to get those original numbers back.
I'm on keep on doing and what Mike shows us his method.
Mike: Alright! Now that was a good one, not bringing that format over with the paste special values.
Another way to do this is, I would do the same thing equals 1 plus That minus 35%, and then I just create a new column.
New price.
I'm going to click here and come up to the home Ribbon and grab that little thing and click right there, and I'm going to do a simple formula equals one cell to my left times this 65%.
Now, I need to lock this and since I'm going down across the numbers, the rows.
I'm going to hit [ F4 ] twice, so the dollar signs adjust in front of the number.
[ ctrl + enter ] to keep that cell highlighted then double click and send the fill handle down.
Hey! Did that work?
Six it did.
Look at that the other trail right here, we started with 10, it goes to 6.50.
MrExcel: That's a good one.
Alright! Sweet there you have it.
Two different ways to solve that problem. Oh, hey!
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
