Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Want to Increase Prices by Percent
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. -- ShirleyF |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Want to Increase Prices by Percent
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 "Shirley" wrote in message ... 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. -- ShirleyF |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Want to Increase Prices by Percent
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. -- Thanks Suleman Peerzade "Shirley" wrote: 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. -- ShirleyF |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Want to Increase Prices by Percent
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 Kuunders" wrote: 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 "Shirley" wrote in message ... 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. -- ShirleyF |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Want to Increase Prices by Percent
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? -- ShirleyF "Bill Kuunders" wrote: 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 "Shirley" wrote in message ... 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. -- ShirleyF |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Want to Increase Prices by Percent
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)? -- ShirleyF "Suleman Peerzade" wrote: 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. -- Thanks Suleman Peerzade "Shirley" wrote: 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. -- ShirleyF |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Want to Increase Prices by Percent
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 a C5 thru C200 D5 thru D200 E5 thru E200 -- ShirleyF "Shirley" wrote: 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. -- ShirleyF |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Want to Increase Prices by Percent
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 ToolsOptionsCalculation set to "Automatic"? Gord Dibben MS Excel MVP On Thu, 27 Dec 2007 04:18:01 -0800, Shirley wrote: 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 a C5 thru C200 D5 thru D200 E5 thru E200 |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Want to Increase Prices by Percent
Gord Dibben wrote:
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 ToolsOptionsCalculation set to "Automatic"? Gord Dibben MS Excel MVP On Thu, 27 Dec 2007 04:18:01 -0800, Shirley wrote: 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 a C5 thru C200 D5 thru D200 E5 thru E200 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 |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Want to Increase Prices by Percent
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. -- ShirleyF "Gord Dibben" wrote: 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 ToolsOptionsCalculation set to "Automatic"? Gord Dibben MS Excel MVP On Thu, 27 Dec 2007 04:18:01 -0800, Shirley wrote: 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 a C5 thru C200 D5 thru D200 E5 thru E200 |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Want to Increase Prices by Percent
Format the empty cell to General.
Copy it. Select all the cells to change. EditPaste Special(in place)AddOKEsc. 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 On Fri, 28 Dec 2007 04:52:00 -0800, Shirley wrote: 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. |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Want to Increase Prices by Percent
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. -- ShirleyF "Gord Dibben" wrote: Format the empty cell to General. Copy it. Select all the cells to change. EditPaste Special(in place)AddOKEsc. 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 On Fri, 28 Dec 2007 04:52:00 -0800, Shirley wrote: 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. |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Want to Increase Prices by Percent
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)addokEsc - it leaves the cells blank. Stay with me I think we are on the right track. -- ShirleyF "Gord Dibben" wrote: Format the empty cell to General. Copy it. Select all the cells to change. EditPaste Special(in place)AddOKEsc. 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 On Fri, 28 Dec 2007 04:52:00 -0800, Shirley wrote: 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. |
#14
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Want to Increase Prices by Percent
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 "Shirley" wrote in message ... 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)addokEsc - it leaves the cells blank. Stay with me I think we are on the right track. -- ShirleyF "Gord Dibben" wrote: Format the empty cell to General. Copy it. Select all the cells to change. EditPaste Special(in place)AddOKEsc. 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 On Fri, 28 Dec 2007 04:52:00 -0800, Shirley wrote: 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. |
#15
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Want to Increase Prices by Percent
Make sure you are following AddOKEsc.
Do not hit the ENTER key after OK or the cells will go blank. Gord On Sat, 29 Dec 2007 05:04:01 -0800, Shirley wrote: But, in your instructions: Paste Special (in place)addokEsc - it leaves the cells blank. |
#16
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Want to Increase Prices by Percent
Wrong instructions..........
Enter 1.4 in the cell then Copy Select cells to change and EditPaste SpecialMultiplyOKEsc. Gord On Sat, 29 Dec 2007 11:45:42 -0800, Gord Dibben <gorddibbATshawDOTca wrote: Make sure you are following AddOKEsc. Do not hit the ENTER key after OK or the cells will go blank. Gord On Sat, 29 Dec 2007 05:04:01 -0800, Shirley wrote: But, in your instructions: Paste Special (in place)addokEsc - it leaves the cells blank. |
#17
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Want to Increase Prices by Percent
Gord
I think the Shirley needs MultiplyOKEsc (not add). George Gee "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Make sure you are following AddOKEsc. Do not hit the ENTER key after OK or the cells will go blank. Gord On Sat, 29 Dec 2007 05:04:01 -0800, Shirley wrote: But, in your instructions: Paste Special (in place)addokEsc - it leaves the cells blank. |
#18
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Want to Increase Prices by Percent
Gord
Sorry, just seen your "update". George Gee "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Make sure you are following AddOKEsc. Do not hit the ENTER key after OK or the cells will go blank. Gord On Sat, 29 Dec 2007 05:04:01 -0800, Shirley wrote: But, in your instructions: Paste Special (in place)addokEsc - it leaves the cells blank. |
#19
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Want to Increase Prices by Percent
No problem.
Need a slap upside the head quite often. Just managed this time to slap my ownself<g Gord On Sat, 29 Dec 2007 20:03:32 -0000, "George Gee" wrote: Gord Sorry, just seen your "update". George Gee "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Make sure you are following AddOKEsc. Do not hit the ENTER key after OK or the cells will go blank. Gord On Sat, 29 Dec 2007 05:04:01 -0800, Shirley wrote: But, in your instructions: Paste Special (in place)addokEsc - it leaves the cells blank. |
#20
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Want to Increase Prices by Percent
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. -- ShirleyF "Gord Dibben" wrote: No problem. Need a slap upside the head quite often. Just managed this time to slap my ownself<g Gord On Sat, 29 Dec 2007 20:03:32 -0000, "George Gee" wrote: Gord Sorry, just seen your "update". George Gee "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Make sure you are following AddOKEsc. Do not hit the ENTER key after OK or the cells will go blank. Gord On Sat, 29 Dec 2007 05:04:01 -0800, Shirley wrote: But, in your instructions: Paste Special (in place)addokEsc - it leaves the cells blank. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
increase different prices by a certain percentage all at once | Excel Worksheet Functions | |||
Percent increase | Excel Discussion (Misc queries) | |||
Percent increase | Excel Worksheet Functions | |||
Increase prices accross the board | Excel Worksheet Functions | |||
Percent Increase | Excel Discussion (Misc queries) |