Exponential decreasing percentage with a floor

GlenCoco

New Member
Joined
Mar 3, 2018
Messages
2
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
200020
250020
300020

<tbody>
</tbody>












Thank you,
Glen
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,848
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

New Member
Joined
Mar 3, 2018
Messages
2
0200
200200
500150
100077
200020

<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

Well-known Member
Joined
May 24, 2005
Messages
3,848
Please post that solution. It is always interesting to see how others solve a problem
 

Watch MrExcel Video

Forum statistics

Threads
1,109,004
Messages
5,526,225
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top