Microsoft Excel - Formula to calculate commission payment

Asked By Kathy Hughes on 15-Apr-13 02:15 PM
       
indicate y if inv. paid
Inv. # Inv. Date Amt. Paid Comm. % Comm. Amt. Date Pd.
10 1-Jan-12 10000.00 y 5.00% 500.00 1-May-12
       
If the date in Column G (Date Pd.) is greater than six months after date in Column B (Inv. Date), the Comm. % reduces by 50% (instead of 5% it will be 2.5%)……what should be added to the formula in Column F (Comm. Amt.) to reflect the correct commission amount of $250.00 if this should be the case?
Harry Boughen replied to Kathy Hughes on 16-Apr-13 02:35 AM
Hi Kathy,
It depends on how accurate you want to be in determining the passage of months.
One simple way is:
=IF((MONTH(G3)-MONTH(B3))<6,C3*E3,C3*E3*0.5)
If you want to have it to the day:
=IF(AND((MONTH(G3)-MONTH(B3))>5,DAY(G3)>DAY(B3)),C3*E3*0.5,C3*E3)
I haven't tried all possibilities and there is a problem after a start date of June so I will have to think a bit more about that so this is very much an interim solution.
Regards
Harry
Harry Boughen replied to Kathy Hughes on 16-Apr-13 02:47 AM
Hi again Kathy,
=IF(AND((MONTH(G3)+12*(MONTH(B3)>=6)-MONTH(B3))>5,DAY(G3)>DAY(B3)),C3*E3*0.5,C3*E3)
This should cover most cases.
Regards
Harry
Kathy Hughes replied to Harry Boughen on 16-Apr-13 07:57 AM
Thank you very, very much for your help Harry.

Kathy
Kathy Hughes replied to Harry Boughen on 16-Apr-13 09:03 AM
Harry,

How would I combine your formula with the existing one in that cell:

=IF(D16=$D$2,(C16*E16),0)

Thanks,

Kathy
Harry Boughen replied to Kathy Hughes on 16-Apr-13 04:50 PM
Hi Kathy,
Yes, just substitute all of mine for the C*E part (except for the equals sign of course).
Regards
Harry
Kathy Hughes replied to Harry Boughen on 17-Apr-13 11:27 AM
Thank you again.

Kathy