Excel Formulas and Functions

[Lenny Johnson]

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

 

[Jesse Hamilton]

=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

 

[Steve Culhane [MS]]

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
(e-mail address removed)
Microsoft Developer Support

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

 

[Tom Ogilvy]

Assuming a profit is made,

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