Exponential decreasing percentage with a floor

[GlenCoco]

I've learned that (almost) anything is possible in Excel, so I'm confident someone knows the solution to this

I'm working on a margin markup formula for our customer reps to use, but the markup percent needs to exponentially decrease as the hard pre-quote value rises, until reaching a set FLOOR percent.

So, as an example:

Pre-Quote $	Min % Markup
200	200
500
1000
2000	20
2500	20
3000	20

<tbody>
</tbody>

Thank you,
Glen

 

[pbornemeier]

0	200
200	200
500	150
1000	77
2000	20

<tbody>
</tbody>

Make a table similar to above that contains the breakpoints you are interested in. For the purposes of the following formula the table is in D1:E5

In B1 use this cell formula =VLOOKUP(A1,D1:E5,2,TRUE)
Put your Pre-Quote $ in A1 to see the Min% Markup in B1

 

[GlenCoco]

0	200
200	200
500	150
1000	77
2000	20

<tbody>
</tbody>

Make a table similar to above that contains the breakpoints you are interested in. For the purposes of the following formula the table is in D1:E5

In B1 use this cell formula =VLOOKUP(A1,D1:E5,2,TRUE)
Put your Pre-Quote $ in A1 to see the Min% Markup in B1

Thanks pbornemeier,
I have also received a solution from a colleague which allows for a smoother transition up-to and breaching value thresholds.

Cheers,
Glen

 

[pbornemeier]

Please post that solution. It is always interesting to see how others solve a problem