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

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

On 1/4/2013 at 1:51:56 PM wrote:


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

On Friday, January 4, 2013 12:54:46 PM UTC-8, tb wrote:
On 1/4/2013 at 1:51:56 PM wrote:





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.

--

tb

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

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.

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

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