I need to create a furmela for a business commission

Question

 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

 

Answer 1

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):

 

     R(x) =  0.05, if x =< 500,000

               [(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:

 

     R(x) =  c, if x =< L

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

               f, if 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:

 

     if([condition], [value if true], [value if false])

 

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!