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
(28 ratings)

Sean B.

Premier NYC Math tutor - young, passionate and talented

New York, NY

4.8
(30 ratings)

Dana R.

English/Math SAT PrepTutor with PhD

Lynbrook, NY

4.8
(85 ratings)

- Math Help 5022
- Algebra 4722
- Math Word Problem 4054
- Calculus 2083
- Word Problem 4839
- Algebra 2 3237
- Algebra 1 3850
- Math Problem 946
- Math Help For College 1340
- Math Equations 930

## Comments