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!

Mark D.

Quantitative research SPECIALIST with academic/professional experience

New York, NY

5.0
(148 ratings)

John P.

Tutor of math and physics, recent college graduate

Short Hills, NJ

5.0
(21 ratings)

- Math Help 5398
- Algebra 5027
- Math Word Problem 4443
- Calculus 2189
- Word Problem 5060
- Algebra 2 3445
- Algebra 1 4035
- Math Problem 1011
- Math Help For College 1389
- Math Equations 956

## Comments