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!

Kimberly P.

Get Ready For Next Year With a Principal-Recommended Tutor!

Rego Park, NY

4.8
(27 ratings)

Wesley L.

Premier MIT Math Tutor and SHSAT/SAT/ACT Specialist

New York, NY

5.0
(228 ratings)

Michael E.

H.S. Mathematics Tutor (Certified NJ Teacher)

Saddle Brook, NJ

5.0
(112 ratings)

- Math Help 4831
- Algebra 4541
- Math Word Problem 3801
- Calculus 2013
- Word Problem 4700
- Algebra 2 3144
- Algebra 1 3718
- Math Problem 911
- Math Help For College 1298
- Math Equations 923

## Comments