# Exponential decreasing percentage with a floor

#### GlenCoco

##### New Member
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

### 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
 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
 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

##### Well-known Member
Please post that solution. It is always interesting to see how others solve a problem

Replies
9
Views
569
Replies
4
Views
484
Replies
2
Views
437
Replies
6
Views
463