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