I found the following formula, using interpolation. It will start by
assigning a 50% markup to $1.00 and the percentage will go down from there
until it reaches $39.99; and will start at 12% for anything above $40.00.

=IF(A1<=39.99,(-A1/100+0.50+1/100),0.12)

I would like to know how I can add to the formula so that it starts and ends
at 14 different intervals:

$1.00-$1.99 assign 50%
$2.00-$2.99 assign 49%
$3.00-$3.99 assign 48%
$4.00-$4.99 assign 47%
$5.00-$5.99 assign 46%
$6.00-$6.99 assign 45%
$7.00-$7.99 assign 44%
$8.00-$8.99 assign 43%
$9.00-$9.99 assign 42%
$10.00-$10.99 assign 41%
$11.00-$11.99 assign 40%
$12.00-$12.99 assign 39%
$13.00-$13.99 assign 38%
$14.00-$14.99 assign 37%
$15.00-$15.99 assign 36%
$16.00-$16.99 assign 34%
$17.00-$17.99 assign 32%
$18.00-$18.99 assign 30%
$19.00-$19.99 assign 25%
$20.00-$24.99 assign 20%
$25.00-$29.99 assign 18%
$30.00-$34.99 assign 16%
$35.00-$39.99 assign 14%
$40.00+ assign 12%

Thank you.

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.