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

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

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