Possible to change/update values in range of cells by X% ?

  • Thread starter Thread starter Tom McLean
  • Start date Start date
T

Tom McLean

Is it possible to update or change the values in a range of cells by a
certain percentage? What I'm trying to do is apply a 10% increase to a column
of prices (several hundred cells long) without doing it manually one by one
or creating a new column (B) that references the values in the first column
(A) and then manipulates the values by X%.

Basically, I'm supposed to update the price list without changing it's
present format. I expect to need to do this again in the future several times
and it would be wonderful if there was some way to do it quickly and easily.

Thanks for any ideas!!

Tom
 
Enter 1.1 in a blank cell.
Copy the cell.
Select the column of prices.
Go to... Edit (menu) | Paste Special | Multiply (button) - click Ok.
Press the Escape key.
Delete 1.1 in the blank cell.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Tom McLean"
wrote in message
Is it possible to update or change the values in a range of cells by a
certain percentage? What I'm trying to do is apply a 10% increase to a column
of prices (several hundred cells long) without doing it manually one by one
or creating a new column (B) that references the values in the first column
(A) and then manipulates the values by X%.

Basically, I'm supposed to update the price list without changing it's
present format. I expect to need to do this again in the future several times
and it would be wonderful if there was some way to do it quickly and easily.

Thanks for any ideas!!
Tom
 
Jim,

Thanks VERY much for this quick tip. You solution is both simple and elegant
in its implementation!

--Tom
 
Jim,

I have the exact same task as Tom. The only difference is that I need to
change column totals by 15%. I tried to follow along with the example you
gave Tom but unfortunately I don't have the multiplication option in the
"paste special" I am using excel 2003.

Help!!!!
Lee
 
Jim,

I got it to work except It only updated my prices by 10% using 1.1 in a
blank cell. I couldn't figure out what number to use to get 15% increase in
prices. I could really use your help.
Lee
 
I'm sorry, but your really need to understand basic arithmetic before you
start trying to use a tool like Excel. If you don't understand broadly what
calculation you are asking Excel to do, you won't know whether you've got
the right answer (which generally means that you may not have asked the
right question).

A percentage is a fraction of one hundred.
10% is 10 divided by 100, and that is represented in the decimal system as
0.1.
If you want to increase a number by 10%, you want to add 10% of the original
number to the original number.
Hence you need to multiply the original number by 0.1, and add that to the
original number.
That is the same as multiplying the original number by 1.1. [... as 1 + 0.1
= 1.1]

Now try doing the same with your 15%.

I would heartily recommend that you find a night-school class or something
similar on basic arithmetic before you try to do too much more with Excel.

Good luck!
 
David,

Thanks for taking the time to explain this to me. Math has never been my cup
of tea. Sadly, I have a Masters degree in Counseling. This little project
that I am working on is to update prices on my website for my newly acquired
online business. I do wish I had the time to take classes but with a wife 2
kids and commuting more than 2.5 hours from home(there and back), there is
just no time. If I did the calculation correct I get 1.15 for 15%
Lee



David Biddulph said:
I'm sorry, but your really need to understand basic arithmetic before you
start trying to use a tool like Excel. If you don't understand broadly what
calculation you are asking Excel to do, you won't know whether you've got
the right answer (which generally means that you may not have asked the
right question).

A percentage is a fraction of one hundred.
10% is 10 divided by 100, and that is represented in the decimal system as
0.1.
If you want to increase a number by 10%, you want to add 10% of the original
number to the original number.
Hence you need to multiply the original number by 0.1, and add that to the
original number.
That is the same as multiplying the original number by 1.1. [... as 1 + 0.1
= 1.1]

Now try doing the same with your 15%.

I would heartily recommend that you find a night-school class or something
similar on basic arithmetic before you try to do too much more with Excel.

Good luck!
--
David Biddulph


Bluesman said:
Jim,

I got it to work except It only updated my prices by 10% using 1.1 in a
blank cell. I couldn't figure out what number to use to get 15% increase
in
prices. I could really use your help.
Lee
Click to expand...
Click to expand...
 
HI Jim.

Could you help me. I have a similar problem to the one you answered below
some time ago. Searched the web for answers you see.

I have a column of values say from b3:b23 and I need to reduce these by 15%.
Is there anyway I can get a formula that just updates the values all at the
same time without using another column to show the values in. I tried to use
the formula you gave below and I can update each cell using the formaula for
example =b3*1.15 but that would be cumbersome to do that for over a hundred
values. Unfortuantely for some reason I to dont have the multiply option when
you go to paste special etc. It simply gives the option of pasting either in
text or unicode.

Any help
 
Hi Keeno

If you want to reduce by 15%, then you would multiply by 0.85 not by 1.15
Jim's suggestion of entering a value in a cell (0.85) copying it then
Selecting your range of data and paste Special>Multiply will work.

If you are not getting the normal Paste Special options, it sounds as though
you have 2 instances of Excel loaded.
Close the instance that you are not using, and you should get the normal
Paste Special options.
--
Regards
Roger Govier

Keeno said:
HI Jim.

Could you help me. I have a similar problem to the one you answered below
some time ago. Searched the web for answers you see.

I have a column of values say from b3:b23 and I need to reduce these by
15%.
Is there anyway I can get a formula that just updates the values all at
the
same time without using another column to show the values in. I tried to
use
the formula you gave below and I can update each cell using the formaula
for
example =b3*1.15 but that would be cumbersome to do that for over a
hundred
values. Unfortuantely for some reason I to dont have the multiply option
when
you go to paste special etc. It simply gives the option of pasting either
in
text or unicode.

Any help



__________ Information from ESET Smart Security, version of virus
signature database 4830 (20100203) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4830 (20100203) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
That was awesome!!! I never knew how to do that and you just saved me about
45 minutes!! Thanks!!!
 
Back
Top