Fomula for computing individual mark up on parts

[Patrick]

I am trying to create a formula which will recognize the amount of a part and
mark it up according to the mark up table I create. For instance, I would
like a .50 part marked up to $1.50 automatically by entering it in the table.
I would like a $10.00 part to be marked up to $20.00 dollars. I have approx
35 lines ranging from a part that costs .01 to parts that cost $5,000.00. I
would like to graduate the markup from a multiplier of 6.0 to a multiplier of
1.2. Does anyone have any suggestions?

 

[Toppers]

A VLOOKUP table might be a possibility if the mark ups can be defined by
price bands
e.g up to $10 mark up is x%, up to $50, mark up is y% etc.

Does this fit your requirement?

 

[Patrick]

Hi Toppers,

It should work. I can email you a copy of what I would like to do and see
if you think it will be possible. Should I just search vlookup table in the
help section?

 

[Toppers]

Send a copy to toppers<at>johntopley.fsnet.co.uk.

But do have look at VLOOKUP in HELP .. you will need to organise your data
in ascending sequence something like:

0 1%
10 3%
50 4%
200 7%

Assuming the above is in columns A & B, and the lookup value is in F1 then
the formula will be:

=VLOOKUP(F1,$A:$B,2,1)

So a value of 0 to 9.99 will return 1%, 10 to 49.99 3% .... and 200+ will
be 7%.

HTH