Excel Formulas and Functions
 

Help, my company pays Commission to sales people based on
the percentage of gross profit per order, the commission
earned is therefor variable. Example, the commision paid
is the same as the percentage of gross profit with a max
of 37% commission, 25% profit= 25% of the profit as
commission, 42% profit= 37% of the profit as commission.
I need help with a formula that will put a cap on the
commission at 37%. I have my sales and commission report
on a spreadsheet now.
Please, can anyone help me with a formula.

Thank you Very Much,
Lenny Johnson

Excel Formulas and Functions
 

=if(commission<=0.37,profitmargin*grossprofit,0.37 *grossprofit)

Should work. Might take some extra steps if the gross profit and percentage
are not in their own columns and will depend on if you're calculating gross
margin or markup percentage.

Jesse Hamilton




"Lenny Johnson" wrote in message
...
Help, my company pays Commission to sales people based on
the percentage of gross profit per order, the commission
earned is therefor variable. Example, the commision paid
is the same as the percentage of gross profit with a max
of 37% commission, 25% profit= 25% of the profit as
commission, 42% profit= 37% of the profit as commission.
I need help with a formula that will put a cap on the
commission at 37%. I have my sales and commission report
on a spreadsheet now.
Please, can anyone help me with a formula.

Thank you Very Much,
Lenny Johnson

Excel Formulas and Functions
 

Lenny,
Use a conditional if statement. Here's the format.

IF(LOGICAL TEST, Value true, Value False)

So if I wanted to test and see if a number was less than 12, and set a cell
equal to 0 or 1 depending on that then I'd do this.

= if(A3 < 12, 1, 0)


Let me know if this helps.


Stephen Culhane

Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Excel Formulas and Functions
 

Assuming a profit is made,

=min(.37,percent profit)*amount profit

--
Regards,
Tom Ogilvy

"Lenny Johnson" wrote in message
...
Help, my company pays Commission to sales people based on
the percentage of gross profit per order, the commission
earned is therefor variable. Example, the commision paid
is the same as the percentage of gross profit with a max
of 37% commission, 25% profit= 25% of the profit as
commission, 42% profit= 37% of the profit as commission.
I need help with a formula that will put a cap on the
commission at 37%. I have my sales and commission report
on a spreadsheet now.
Please, can anyone help me with a formula.

Thank you Very Much,
Lenny Johnson