and a percentage to an existing amount

[darkan9el]

Hi all

I have worksheet with prices, every year I have to add a percent onto those prices. at the moment I just change the prices one by one. but I wondered if I could have a cell with a percent in it I could change and it would amend all the prices in one go.

How can I do this please?

[oldchippy]

Select a blank cell on your sheet insert say 1.5%, then copy, select your range of cells, right click > paste Special > Multiply

[darkan9el]

Hi, thanks for the reply, just tried that and it gave me the percentage.

i.e. I typed 10% into A1 I then chose a cell with £10.00 in it, the result was £1.00 which is 10% of £10.00

Based on that, I did 110% and I got £11.00 which would be correct if I wanted to add 10% to that price, so thats a work around at least. Cool.

I think I still need a more elegant way to do this though, but at least for the time being I have a quick and dirty way to change prices en mass.

Thankyou

[BruceP]

I don't know if this is "more elegant", but I find the approach useful. In many workbooks, I use a hidden sheet for rough figuring, storing useful data, etc. I usually call this sheet "StdData", for "standard data".

In this situation, you could name a cell "pct", and populate it with a value of 0%. You could then enter all of your values that will be multiplied by this percent in the future.

On your "display" sheet (whatever sheet you or your customers would see), you can put in a simple formula that will multiply your target value by the value in 'pct'. Then, you only have to change the single value in 'pct' at the end of the year.

For example:
'pct' is cell StdData!A1, and contains the value 0%.
Your prices are stored in cells StdData!A2:A10

On your presentation sheet, you could have the following formula in A2:
=StdData!A2*(1+pct)

...and carry that formula down as far as you need it. As long as 'pct' = 0%, you're basically saying, "value * 1". If you increase 'pct', (say, 15%), then you're saying "value * 1.15".

Not very elegant, but very useful - especially for complex issues where you don't want to use VBA code.

HTH,

Bruce

[darkan9el]

Hi thanks for the Idea, just played with it and applied the technique, work fine.

thanks all for your help

[BruceP]

Any time...thanks for the feedback.