Thank you so much for your help.

Ed.

"Spiky" wrote in message
...
On Aug 27, 2:18 pm, "Ed" wrote:
In Excel, I am looking for a formula to apply a sliding scale price
markup
using interpolation.

The size of the percentage markup would decrease as cost increases and
would
increase as cost decreases.

I have 200 products and the cost ranges from $1.50 (approx. 60% markup)
to
$35.00 (approx. 15% markup)

Avg. cost is $6.00 (50% markup)

Thank you.

Ed.

Guess it depends how you want to do it. You need to select an amount
where there is a change in the markup %. It may be every $1, or a true
sliding scale would use your smallest possible dollar increment on
your cost list. So maybe $.01 or $.001.

And, I'd probably find the best would be to make a simple table,
although you could cram all this into a formula. Personally, I'd want
the table to have for printing or visually checking a markup on
occasion, anyway.

List all your prices and the markup in 2 columns. Then use INDEX/MATCH
in your invoice worksheet to get the proper markup. If you use more of
a jump than your smallest increment, make sure to put $35 at the top
and $1.50 at the bottom to allow the formula to work best. By $.25
increments, and rounded for sanity:
..A...........B
35.00....15.00%
34.75....15.34%
34.50....15.67%
34.25....16.01%

Lookup function to pull the markup, assuming E2 is your price:
=INDEX($A$1:$B$135,MATCH(E2,$A$1:$A$135,-1),2)

And the formula to figure out the difference is just simple division.
Well, 2 of them. Using my $.25 increment:
(60%-15%)/((35-1.50)/.25)=.003358209

Add that to each level of markup. So the first dollar level has 15%
markup, the 2nd is 15.34%, 15.67%, etc. (rounded, of course)



Or you could go truly nuts and base it on percentage of products above
and below your average cost of $6. Have it be a weighted sliding
scale. That would be more complex and you didn't give enough data to
play with that.