Home |
Search |
Today's Posts |
#1
|
|||
|
|||
help with excel
I am new here so please excuse any mistakes in this posting I wonder if anyone out there can help me with an Excel problem i am having.... i want Excel to calculate a commission based on the following information the these columns: gross sales less expenses net profit % of profit commission the first part is easy and i can handle that...its the calculation of the commission that is giving me problems. The calculation of commissions is as follows: if the percentage of profit is 20% then that is the commission amount eg: 20% of the net profit if the percentage of profit is less than 20% then the commission amount is whatever that figure is: eg 19.8% times the net profit if the percentage of profit is more than 20% then the commission amount is 20% of the net profit. eg: percentage of profit is 21.3% then the commission is 20% of the net profit... if there is a loss (net profit greater than the sales) then the commission should reflect a negative percentage and therefore be deducted from any commissions due i think that covers all the possibilities Thanks in advance for any help you can give with this Bert Hayford -- daniel4214 ------------------------------------------------------------------------ daniel4214's Profile: http://www.excelforum.com/member.php...o&userid=26931 View this thread: http://www.excelforum.com/showthread...hreadid=401556 |
#2
|
|||
|
|||
try
=min(prof percent,.2)*netprofit for your commision "daniel4214" wrote: I am new here so please excuse any mistakes in this posting I wonder if anyone out there can help me with an Excel problem i am having.... i want Excel to calculate a commission based on the following information the these columns: gross sales less expenses net profit % of profit commission the first part is easy and i can handle that...its the calculation of the commission that is giving me problems. The calculation of commissions is as follows: if the percentage of profit is 20% then that is the commission amount eg: 20% of the net profit if the percentage of profit is less than 20% then the commission amount is whatever that figure is: eg 19.8% times the net profit if the percentage of profit is more than 20% then the commission amount is 20% of the net profit. eg: percentage of profit is 21.3% then the commission is 20% of the net profit... if there is a loss (net profit greater than the sales) then the commission should reflect a negative percentage and therefore be deducted from any commissions due i think that covers all the possibilities Thanks in advance for any help you can give with this Bert Hayford -- daniel4214 ------------------------------------------------------------------------ daniel4214's Profile: http://www.excelforum.com/member.php...o&userid=26931 View this thread: http://www.excelforum.com/showthread...hreadid=401556 |
#3
|
|||
|
|||
bj -
=min(prof percent,.2)*netprofit That will result in a positive commission for negative profits Maybe =if(prof percent .2,.2,abs(prof percent))*net profit "bj" wrote: try =min(prof percent,.2)*netprofit for your commision "daniel4214" wrote: I am new here so please excuse any mistakes in this posting I wonder if anyone out there can help me with an Excel problem i am having.... i want Excel to calculate a commission based on the following information the these columns: gross sales less expenses net profit % of profit commission the first part is easy and i can handle that...its the calculation of the commission that is giving me problems. The calculation of commissions is as follows: if the percentage of profit is 20% then that is the commission amount eg: 20% of the net profit if the percentage of profit is less than 20% then the commission amount is whatever that figure is: eg 19.8% times the net profit if the percentage of profit is more than 20% then the commission amount is 20% of the net profit. eg: percentage of profit is 21.3% then the commission is 20% of the net profit... if there is a loss (net profit greater than the sales) then the commission should reflect a negative percentage and therefore be deducted from any commissions due i think that covers all the possibilities Thanks in advance for any help you can give with this Bert Hayford -- daniel4214 ------------------------------------------------------------------------ daniel4214's Profile: http://www.excelforum.com/member.php...o&userid=26931 View this thread: http://www.excelforum.com/showthread...hreadid=401556 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Group" function very slow with Excel 2003 :( ... While very quick with Excel2000 :O) | Excel Discussion (Misc queries) | |||
Stop Excel Rounding Dates | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Excel error - Startup (and Acrobat PDFMaker) | Setting up and Configuration of Excel | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) |