Want to Increase Prices by Percent

  • Thread starter Thread starter Shirley
  • Start date Start date
S

Shirley

New to this. Am using Office-Excel 2000. Have entered three columns of prices
for company (List, Dealer, Distributor). Prices will increase next month.
How do I increase all rows (example C3-C50) by 10 percent?
Will need to do this for all three columns.
 
one way

1.. In an empty cell, enter the number that you want to multiply by.....
1.10
2.. Select that cell, and on the Edit menu, click Copy.
3.. Select the whole range of numbers that you want to multiply.
4.. On the Edit menu, click Paste Special.
5.. Under Operation, click Multiply.
6.. Click OK.
7.. Delete the content of the cell entered in the first step.
Greetings from New Zealand
 
Hi Shirley,

This might help you.
1. For eg the existing data is in Sheet 3 and you want to increase the prise
by 10% in all the columns as mentioned by you.
2. Give a name to the existing work sheet so that you can track the change
in price.
3. Go to Toolbar/Insert/Worksheet.
4. For eg the existing sheet is renamed as December and the prices will
change in January 08. Then rename the new worksheet as Jan 08. Then copy the
headings and data of the previous sheet in this new sheet and keep the cells
of price columns empty. Enter the below mentioned formula in it.

='Dec 07'!A2*10%+'Dec 07'!A2 . Here the percentage can be changed to any
number for eg 15%.

You can change the cell reference number like A3 or D3 in the formula
depending upon where the data for ListPrice,DealerPrice and DistributorPrice
is entered.

Once you get the require figure in the require cell then simply paste this
formula in column cells and you will get the results increased by the
percentage you had typed.
 
Bill, a further question on the subject if you don't mind . . .

A problem I have dealing with currency, is that I've found no way to use
Paste Special Multiply and get the result rounded. Can that be done?
 
Bill:
Thanks for suggestion and it makes sense.
However, I took the steps you recommend and when I select "Paste Special"
from th Edit menu the only options I get are either Unicode Text or Text. I
have seen the screen with the Operation Multiply etc before, but in this case
I don't get it.
Am I doing something wrong or have something turned off?
 
Suleman:
Thanks for suggestion.
I tried the example formula you suggested.
It worked for an individual cell, but not for a range of cells. When I tried
a range of cells the same total appeared in all the cells.
Is there some way in the formula to specify a range (example C5 thru C200)?
 
For Bill andSuleman:
Appreciate both your responses and tried both suggestions.
Reference Bill suggestion - when I get to the point of Edit-Paste Special I
do not get the window with all the Operations on it. Instead under Paste
Special I just get two options to paste as Unicode Text or Text. Dont know
what the problem is.

Reference Suleman suggestion - when I use the formula it correctly gave me
the result in one cell only (the first cell). When I used the formula in a
range of cells it gave me the same result in all cells which of course is
incorrect.

Any addtional suggestions would be most appreciated.
If it is any help the cells I am trying to add 10% to each one are:
C5 thru C200
D5 thru D200
E5 thru E200
 
Shirley.

Are you entering the 1.10 in an unused cell in the same worksheet as your range
of data to convert?

From your description it sounds like you have two insatnces of Excel open and
you are copying from one to the other.

For the second problem with the formulas, do you have Tools>Options>Calculation
set to "Automatic"?


Gord Dibben MS Excel MVP
 
Gord said:
Shirley.

Are you entering the 1.10 in an unused cell in the same worksheet as your range
of data to convert?

From your description it sounds like you have two insatnces of Excel open and
you are copying from one to the other.

For the second problem with the formulas, do you have Tools>Options>Calculation
set to "Automatic"?


Gord Dibben MS Excel MVP
Click to expand...

Gordon,
I noticed the OP is using Excel 2000. Does it have the same cut and
paste option as later versions? I seem to remember that option was added
in a later version. It's been a long time since I used Excel 2000.

gls858
 
Gord: Yes I have Automatic turned on in Tools-Options-Calculation.
Yes I think I am entering 1.10 in what I hope and think is an unused cell.
It is a blank cell on the same sheet. Is there another way I can try it to
make sure I am using a blank-empty cell?
I will play with it a little more to see.
Also reference old version (2000) I think one of the other girls has Office
2003 on her computer so I will make a copy of this and take it over there to
see if I have any luck.
 
Format the empty cell to General.

Copy it.

Select all the cells to change.

Edit>Paste Special(in place)>Add>OK>Esc.

If you are copying within the same worksheet I do not understand why you would
continue to get the Text or Unicode Text options only.

Those options are usually reserved for when you copy from another non-Office
application to the Windows Clipboard rather than the Office Clipboard.


Gord
 
Tks so much. I will try that today. By the way in your previous post you
mentioned INSTANCE and I also noticed that on the Knowledge Base I was
checking. What is an INSTANCE.
I did try the same thing on another girl's computer that has Excel 2003 and
ran into same problem. So maybe the file is corrupted, but then I created a
brand new worksheet and just entered data into a few cells and still could
not get the operations under Paste Special -- so it is not just the file or
the version of Excel I am using. I am determined to figure this out and I
know it is going to be something very simple which will make me look pretty
stupid. By my background is in DBs (ACT and FileMaker) and I inherited this
Excel thing when I took this job. Apparently the file was created 5 or 10
years ago and the girl before me had been updating the prices manually every
year. I just figured since I do it all the time in a DB that a simple formula
would allow me to update each column at the same time.
Will try your suggestion and let you know what happens. Tks again.
 
Ok, I think we are a step closer. First I noticed all the cells I want to
increase are formatted as CURRENCY.
I tried your suggestion. After I format empty cell to General, copy and
select the cells I want to multiply I DO GET THE PASTE SPECIAL options
including Paste: All, Formats etc (10 options), Operations: None, Add etc (5
options).
At least I am seeing it for the first time.
But, in your instructions: Paste Special (in place)>add>ok>Esc - it leaves
the cells blank.
Stay with me I think we are on the right track.
 
Shirley

1) Enter 1.10 in an empty cell formatted as General.
2) Copy the cell.
3) Select all the cells that you wish to increase.
4) Edit > Paste Special.
5) Dot in "Values", dot in "Multiply", click "OK".
6) Select the original cell, press "Enter".

George Gee
 
Make sure you are following Add>OK>Esc.

Do not hit the ENTER key after OK or the cells will go blank.


Gord
 
Wrong instructions..........

Enter 1.4 in the cell then Copy

Select cells to change and Edit>Paste Special>Multiply>OK>Esc.


Gord
 
No problem.

Need a slap upside the head quite often.

Just managed this time to slap my ownself<g>


Gord
 
Gord and George:
Thanks so much for the help. I found exactly what I wanted on the Help file
in note titled: Examples of commonly used formulas.
It is almost same as what you both suggested, but works around the fact I
could not get the Paste Special full window with operations etc.
Here is what is working for me and it does full range of Cells selected:

In empty cell formatted as number I enter 1.10 (for 10 percent).
Than in another empy cell I enter formula: =C5*(1+$F$5) and copy it. (That
makes the F5 cell active with moving border). Copy my range of cells and
Paste Special with multiply and all my prices I selected are increased by 10
percent. I checked a few to make sure and they were perfect.

Thanks to all for sticking with me on this.
It is really a load off my mind.
 
Back
Top