Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Formulas for logical functions | Excel Discussion (Misc queries) | |||
How do I use functions that I have written in Excel formulas | Excel Worksheet Functions | |||
Excel 2000 - Formulas and Functions | Excel Discussion (Misc queries) | |||
Help with Excel Formulas or Functions | Excel Discussion (Misc queries) | |||
Excel functions/formulas | Excel Programming |