Excel 2000 applying a variable % price increase

P

Pete

Hi

I want to be able to apply a variable percentage formula
that will apply percentage increases to selected cells in
my client's Excel 2000 price list.
eg
Product Range x Product A $30 Product B $45 Product C $75
Product Range y Product A $45 Product B $65 Product C $110
Product Range z Product A $120 Product B $165 Product C$195

Say Product Range x is going up 10%, product range y 15%
& product range z 20%, each range having 3 products.

I am not sure know to apply a formula that will make these
price increases happen.

Ideally my client wants to be able to type a percentage
into a cell formula that will change each price in each
cell in that product range.

To make it even more interesting, the updated price tables
are to be linked to word via the paste link option!

Can you please suggest the steps required to achieve this!

TIA.

Pete.
 
B

Bob Phillips

Hi Pete,

Try something like

=CHOOSE(MATCH(A1,{"p1","p2","p3","p4"},0),10%,15%,20%,25%)

to get the increase percentage, and multiply the price by this

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
P

Pete

Hi Bob

When I type the formula you suggested into the location of
my 1st price (Product A $30(Range X)in this case cell ref
B4)I get an #N/A error message.

Is "P1" the 1st price,"P2" the 2nd etc? If so why are
there 4 "p"s when there is only three product in each
product range?

Please forgive my ignorance...!!

Can I email you the excel file so you can see what I mean?

TIA

Pete
 
B

Bob Phillips

No Pete, the formula I gave only returns the percentage for each product.
The p1, p2 ,p3 is the list of products, and may be shorter or longer. If it
is longer, be sure to add extra percentages. As I said, you multiply the
price by the derived percentage.

By all means mail me the workbook. Watch for the spam trap in my email
address.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
G

Guest

Hi Bob

I seem to be having trouble with sending you the email.I
removed the spam trap as you said but the message bounced
back.

Can you confirm that I have the correct email address?
ie
[email protected]

TIA
Pete
 
B

Bob Phillips

I would rather you hadn't published my unadulterated email address in a
public forum, but yes that is it. You could always post yours here
(adulterated with instructions of course)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

You mean 50 more than the 100 I already get Peo!. At least it is down from a
peak 30 a day.

Bob
 
Top