Dear heros,

i would like to create a exel formula for commercial commissions

which will start from 5% if the deal is =<500,000 then it would be decreased till reach to 2.5% when it equals 3,000,000 or bigger.

Best Regards,

jarrash

Dear heros,

i would like to create a exel formula for commercial commissions

which will start from 5% if the deal is =<500,000 then it would be decreased till reach to 2.5% when it equals 3,000,000 or bigger.

Best Regards,

jarrash

Tutors, please sign in to answer this question.

Oxnard, CA

Hello Jarrash,

I've assumed that you want the commercial commission to decrease linearly from 5% to 2.5% between deals valued at $500k and $3M.

I'm not an Excel wizard, so there may be a better way to do this, but I'd use nested IF() statements to accomplish your goal. First, deconstruct your criteria into a piecewise mathematical function
*R*(*x*):

[(0.025 - 0.05)/(3,000,000 - 500,000)]•(*x* - 500,000) + 0.05, if 500,000 <
*x* < 3,000,000

0.025, if *x* >= 3,000,000

In fact, I'd replace all of those numbers in the formula above with variables defined elsewhere in your sheet, so that you can freely change your values without having to edit the formula:

[(*f* - *c*)/(*U* - *L*)]•(*x* -
*L*) + *c*, if *L* < *x* < *U*

Where *c* is your ceiling commission rate (5%), *f* is your floor commission rate (2.5%), *L* is your lowest threshold for deals that qualify for your ceiling commission rate ($500k), and
*U* is your upper limit for deals protecting your floor commission rate ($3M). The value of the deal you're working with is *x*.

The IF() function for Excel is very simple. It's syntax is:

Your formula would look like:

=**if**(*x* < 0, "Error: Negative deal value", **
if**(*x* <= *L*, *c*, **if**(*x* < *U*, [(*f* - *c*)/(*U* - *L*)]*(*x* - *L*) + *c*, **if**(*x* >= *U*, *f*, "Error, Non-numerical deal value"))))

Replace the variables above with the appropriate cell references. The error messages will hopefully catch any mistakes that might have carried over from other areas of your workbook.

Keep in mind that I consider myself an Excel amateur, and that there may be a more elegant solution to your problem.

Hope this has helped!

- Math Help 6158
- Algebra 5709
- Math Word Problem 5417
- Calculus 2668
- Word Problem 5646
- Algebra 2 3812
- Math Problem 1173
- Algebra 1 4479
- Math Help For College 1534
- Math Equations 998

## Comments