Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
Posts: 84
Default Percentage Increase

I am using Microsoft Excel 2007 (32-bit) on a Windows 7 desktop.

Management has decided to increase the list prices of our products by
an across-the-board, fixed percentage.

Given the variety and options offered with our products, list prices
are calculated using a series of Excel tables, whereby the final list
price is obtained by going to one or more tables and adding the amount
specified in each appropriate cell at the intersection of rows/columns.

Is there a _quick_ method that I can use to add this fixed X percentage
increase to all the cells who have a $ price in them? (Luckily we are
raising all our prices by a single X percentage!)

I am thinking of something like highlighting all the cells of a table
that have $ amounts in them and then issuing some command that would
multiply the contents by this fixed X percentage. This is probably not
possible but one never knows what the Gurus can come up with...

--
tb
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 168
Default Percentage Increase

On Friday, January 4, 2013 11:32:46 AM UTC-8, tb wrote:
I am using Microsoft Excel 2007 (32-bit) on a Windows 7 desktop.



Management has decided to increase the list prices of our products by

an across-the-board, fixed percentage.



Given the variety and options offered with our products, list prices



are calculated using a series of Excel tables, whereby the final list

price is obtained by going to one or more tables and adding the amount

specified in each appropriate cell at the intersection of rows/columns.



Is there a _quick_ method that I can use to add this fixed X percentage

increase to all the cells who have a $ price in them? (Luckily we are

raising all our prices by a single X percentage!)



I am thinking of something like highlighting all the cells of a table

that have $ amounts in them and then issuing some command that would

multiply the contents by this fixed X percentage. This is probably not

possible but one never knows what the Gurus can come up with...



--

tb


Hi tb,

From a google search:
(adjust the ranges and percentage amount to your needs)

Increase Selected Numbers by a Percentage.
You may want to increase all the numbers in a range by a set percentage. For example, in this price list, all the prices should be increased by 5%. The following technique makes it easy to increase the prices, all at once.
In a blank cell, enter the amount of the increase. In this example, 1.05 was entered in cell D8
Copy the cell which contains the increase amount.
Select the cells which contain the amounts that you want to increase. Here, cells B8:B11 are selected.
On the menu bar, click Edit | Paste Special
Click Values, and click Multiply, then click OK.
Each of the selected numbers is automatically increased by 5%

Regards,
Howard
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 829
Default Percentage Increase

wrote:
After you have... "Paste Special Values Multiply OK"
your field of prices that you have just increased should
still be selected. Click Home Number 'click decrease
(or increase) decimal points icon' until two.


That affects only the __appearance__ of the result. The __actual__ value
might still have digits beyond 2 decimal places.

The only way this procedure would alter the __actual__ value is if the
"Precision as displayed" calculation option is set.

If you are already using PAD, that's fine.

Otherwise, it would be misguided and dangerous to set PAD just for this
purpose.

PAD affects the entire workbook as soon as it is set. That is, it might
round unintended cells. If those cells contain constants, they would be
changed irreversibly.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
Posts: 84
Default Percentage Increase

On 1/4/2013 at 6:04:10 PM joeu2004 wrote:


That affects only the appearance of the result. The actual value
might still have digits beyond 2 decimal places.

The only way this procedure would alter the actual value is if the
"Precision as displayed" calculation option is set.

If you are already using PAD, that's fine.

Otherwise, it would be misguided and dangerous to set PAD just for
this purpose.

PAD affects the entire workbook as soon as it is set. That is, it
might round unintended cells. If those cells contain constants, they
would be changed irreversibly.



Yes, I agree. Formatting to two decimals will only mask additional
decimals. I need a way to round to two decimals and then truncate the
rest.

So... I am guessing there is no solution?

--
tb
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 829
Default Percentage Increase

"tb" wrote:
Yes, I agree. Formatting to two decimals will only mask additional
decimals. I need a way to round to two decimals and then truncate the
rest. So... I am guessing there is no solution?


None except the VBA solution that someone provided you in the Microsoft
Community forum.

Of course, if you are going to use VBA, you might as well let it do the
percentage increase as well instead of making it a 2-step process, if your
data are constants.

(Sounds like they are.)

Alternatively, you could set up a Worksheet_Change event macro so that the
VBA code would execute automagically when you perform the
paste-special-multiply operation.

I was tempted to suggest that in the MC forum. But I decided it is too
error-prone. Much safer to execute the macro manually when you need it.

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
Posts: 12
Default Percentage Increase

On 1/4/2013 8:04 PM, joeu2004 wrote:

None except the VBA solution that someone provided you in the Microsoft
Community forum.

Of course, if you are going to use VBA, you might as well let it do the
percentage increase as well instead of making it a 2-step process, if
your data are constants.

(Sounds like they are.)

Alternatively, you could set up a Worksheet_Change event macro so that
the VBA code would execute automagically when you perform the
paste-special-multiply operation.

I was tempted to suggest that in the MC forum. But I decided it is too
error-prone. Much safer to execute the macro manually when you need it.


The idea of applying a percentage increase and rounding/truncating to
two decimals all in a single step is appealing... Unfortunately I know
zero about VBA macros!

Yes my data is constant. Would you do me a favor and show me how I
should amend the VBA script which was suggested in the Microsoft
Community forum so that it also applies the percentage increase?

Sub RoundSelection()
Dim Cell As Range
For Each Cell In Selection
Cell.Value = WorksheetFunction.Round(Cell.Value, 2)
Next Cell
End Sub


Thanks.
--
tb
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 829
Default Percentage Increase

"tb" wrote:
Yes my data is constant. Would you do me a favor and show me how
I should amend the VBA script which was suggested in the Microsoft
Community forum so that it also applies the percentage increase?
Sub RoundSelection()
Dim Cell As Range
For Each Cell In Selection
Cell.Value = WorksheetFunction.Round(Cell.Value, 2)
Next Cell
End Sub


Be sure to make a backup copy of the Excel file before trying anything.

Sub RoundSelection()
Const pctChange As Double = 0.07 ' for 7%
Dim Cell As Range
For Each Cell In Selection
Cell = WorksheetFunction.Round(Cell*(1+pctChange), 2)
Next Cell
End Sub

Change Round to Rounddown to truncate, or to Roundup to round up.

Note: Use WorksheetFunction.Round as shown, not the VBA Round function.
The latter rounds differently ("banker's rounding").

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 168
Default Percentage Increase

On Friday, January 4, 2013 11:32:46 AM UTC-8, tb wrote:
I am using Microsoft Excel 2007 (32-bit) on a Windows 7 desktop.



Management has decided to increase the list prices of our products by

an across-the-board, fixed percentage.



Given the variety and options offered with our products, list prices

are calculated using a series of Excel tables, whereby the final list

price is obtained by going to one or more tables and adding the amount

specified in each appropriate cell at the intersection of rows/columns.



Is there a _quick_ method that I can use to add this fixed X percentage

increase to all the cells who have a $ price in them? (Luckily we are

raising all our prices by a single X percentage!)



I am thinking of something like highlighting all the cells of a table

that have $ amounts in them and then issuing some command that would

multiply the contents by this fixed X percentage. This is probably not

possible but one never knows what the Gurus can come up with...



--

tb

Hi tb,

Joeu2004's last-offered code is most likely as good as it gets or needs to be.

You can try this approach and see if it suits you.
Select your price data range and name it DataP.
Enter the percentage value in cell P1.
(If you want to change the percentage, you can do so on the sheet
and not have to go to the vb editor and change code %.)

Hopefully, Joeu2004 will look at this code and make sure I did not
inadvertently lead you astray.

Run this code.

Option Explicit

Sub RoundSelection()
Dim Cell As Range
Range("P1").Copy
Range("DataP").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False

For Each Cell In Range("DataP")
Cell.Value = WorksheetFunction.Round(Cell.Value, 2)
Cell.NumberFormat = "0.00"
Next Cell

Application.CutCopyMode = False
Range("P1").Select
End Sub

Regards,
Howard


  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 829
Default Percentage Increase

"joeu2004" wrote:
Sub RoundSelection()
Const pctChange As Double = 0.07 ' for 7%
Dim Cell As Range
For Each Cell In Selection
Cell = WorksheetFunction.Round(Cell*(1+pctChange), 2)
Next Cell
End Sub


If you would like the flexibility of putting the percentage change into a
cell, obviating the need to alter the macro, you could write:

Sub roundSelection()
Dim pctChange As Double
Dim Cell As Range
pctChange = Range("pctChange")
For Each Cell In Selection
Cell = WorksheetFunction.Round(Cell * (1 + pctChange), 2)
Next Cell
End Sub

To use the macro:

1. Enter 7% into some cell. (Enter -7% for a percentage decrease.)
2. With that cell selected, enter pctChange into the Name Box (upper left).
3. Select the data to be modified.
4. Press alt+F8 and run the macro roundSelection.
5. You can now delete the value in the cell used for #1.

Note: If you have already done this once and you want to use a different
cell for #1, you should use the Name Manager to delete or alter the previous
"refers to" for the name pctChange. For Excel 2007, click on the Formula
tab, then Name Manager.

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 168
Default Percentage Increase

On Saturday, January 5, 2013 9:24:21 AM UTC-8, joeu2004 wrote:
"joeu2004" wrote:

Sub RoundSelection()


Const pctChange As Double = 0.07 ' for 7%


Dim Cell As Range


For Each Cell In Selection


Cell = WorksheetFunction.Round(Cell*(1+pctChange), 2)


Next Cell


End Sub




If you would like the flexibility of putting the percentage change into a

cell, obviating the need to alter the macro, you could write:



Sub roundSelection()

Dim pctChange As Double

Dim Cell As Range

pctChange = Range("pctChange")

For Each Cell In Selection

Cell = WorksheetFunction.Round(Cell * (1 + pctChange), 2)

Next Cell

End Sub



To use the macro:



1. Enter 7% into some cell. (Enter -7% for a percentage decrease.)

2. With that cell selected, enter pctChange into the Name Box (upper left).

3. Select the data to be modified.

4. Press alt+F8 and run the macro roundSelection.

5. You can now delete the value in the cell used for #1.



Note: If you have already done this once and you want to use a different

cell for #1, you should use the Name Manager to delete or alter the previous

"refers to" for the name pctChange. For Excel 2007, click on the Formula

tab, then Name Manager.


I like...!!!
Not a single .Select in the whole code. Off to my archives with this one as a nice little package for reference.

Regards,
Howard
  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
Posts: 84
Default Percentage Increase

On 1/5/2013 at 11:24:21 AM joeu2004 wrote:


If you would like the flexibility of putting the percentage change
into a cell, obviating the need to alter the macro, you could write:

Sub roundSelection()
Dim pctChange As Double
Dim Cell As Range
pctChange = Range("pctChange")
For Each Cell In Selection
Cell = WorksheetFunction.Round(Cell * (1 + pctChange), 2)
Next Cell
End Sub

To use the macro:

1. Enter 7% into some cell. (Enter -7% for a percentage decrease.)
2. With that cell selected, enter pctChange into the Name Box (upper
left). 3. Select the data to be modified.
4. Press alt+F8 and run the macro roundSelection.
5. You can now delete the value in the cell used for #1.

Note: If you have already done this once and you want to use a
different cell for #1, you should use the Name Manager to delete or
alter the previous "refers to" for the name pctChange. For Excel
2007, click on the Formula tab, then Name Manager.


So... In order to save this macro in my workbook do I have to save the
file with the extension .xlsm instead of .xlsx? Am I right?

The reason I am asking is because these instructions do not say
anything about that:
<http://office.microsoft.com/en-001/excel-help/create-or-delete-a-macro-HP010014111.aspx

--
tb
  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 621
Default Percentage Increase

That is correct.

Gord


On Wed, 3 Dec 2014 21:58:25 +0000 (UTC), "tb"
wrote:

So... In order to save this macro in my workbook do I have to save the
file with the extension .xlsm instead of .xlsx? Am I right?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Percentage increase Lynne Mawson New Users to Excel 2 March 29th 07 02:02 PM
percentage increase Dave F Excel Discussion (Misc queries) 0 January 19th 07 09:22 PM
increase the value of a cell by a percentage ba Excel Discussion (Misc queries) 2 January 5th 06 11:29 PM
percentage increase bigjeffscv Excel Worksheet Functions 1 September 26th 05 11:50 PM
Percentage increase Unionjack jackson Excel Worksheet Functions 2 February 14th 05 03:42 PM


All times are GMT +1. The time now is 10:54 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"