Percentage Increase

[tb]

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...

 

[lhkittle]

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...

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

 

[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

Thanks, Howard!

A follow-up question: Is there a way to round/truncate the results to
two decimals?

Best regards.

 

[lhkittle]

Thanks, Howard!



A follow-up question: Is there a way to round/truncate the results to

two decimals?



Best regards.

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.

Should get you there.

Regards,
Howard

 

[joeu2004]

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.

 

[tb]

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?

 

[joeu2004]

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.

 

[tb]

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.

 

[joeu2004]

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").

 

[lhkittle]

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...

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

 

[joeu2004]

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.

 

[lhkittle]

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

 

[tb]

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>

 

[Gord Dibben]

That is correct.

Gord