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:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Pre-Quote $[/TD]
[TD]Min % Markup[/TD]
[/TR]
[TR]
[TD]200
[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]2500[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]3000[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]












Thank you,
Glen

 

[pbornemeier]

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent, align: right"]200
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]200
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]200
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]500
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]150
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]1000
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]77
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]2000
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]20
[/TD]
[/TR]
</tbody>[/TABLE]
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]

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent, align: right"]200[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]200[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]200[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]500[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]150[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]1000[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]77[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]2000[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]20[/TD]
[/TR]
</tbody>[/TABLE]
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