Dear Friends,
Calculating Commission:
Main sheet: Cells A1 to H5
[TABLE="width: 683"]
<tbody>[TR]
[TD]SALESMAN[/TD]
[TD]SALESMAN NO[/TD]
[TD]BILL NO[/TD]
[TD]BILL DATE[/TD]
[TD]SOLD AMOUNT[/TD]
[TD]PAYMENT RCVD[/TD]
[TD]BALANCE[/TD]
[TD]DAYS[/TD]
[/TR]
[TR]
[TD]JOHN[/TD]
[TD="align: right"]20[/TD]
[TD]AF-001[/TD]
[TD="align: right"]02-04-2018[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]JOHN[/TD]
[TD="align: right"]20[/TD]
[TD]AF-042[/TD]
[TD="align: right"]14-04-2018[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]JOHN[/TD]
[TD="align: right"]20[/TD]
[TD]AF-050[/TD]
[TD="align: right"]29-04-2018[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]JOHN[/TD]
[TD="align: right"]20[/TD]
[TD]AF-201[/TD]
[TD="align: right"]20-06-2018[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]14[/TD]
[/TR]
</tbody>[/TABLE]
Target: Cells K2 to J4
[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]TARGET[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]MONTH[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD]QTR[/TD]
[TD="align: right"]15000[/TD]
[/TR]
</tbody>[/TABLE]
Calculation Desired result: Cells N1 to P3
[TABLE="width: 220"]
<tbody>[TR]
[TD]COMMISSION[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]Jun-18[/TD]
[TD]QTR-1[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]220[/TD]
[/TR]
</tbody>[/TABLE]
Logics: If he achieves Monthly & qtrly target, then eligible for commission, but the invoices which full payment received within 15 days that will be in calculation. In case of monthly commission it will be 5% of bill value & for qtr that will be 2%.
N3, O3, & P3 are my desired result. Pls help me with formulas month wise & qtr wise.
Pls note monthly & qtr achievement will separately be accepted.
Thanks in advance.
Regards
Ramu
Calculating Commission:
Main sheet: Cells A1 to H5
[TABLE="width: 683"]
<tbody>[TR]
[TD]SALESMAN[/TD]
[TD]SALESMAN NO[/TD]
[TD]BILL NO[/TD]
[TD]BILL DATE[/TD]
[TD]SOLD AMOUNT[/TD]
[TD]PAYMENT RCVD[/TD]
[TD]BALANCE[/TD]
[TD]DAYS[/TD]
[/TR]
[TR]
[TD]JOHN[/TD]
[TD="align: right"]20[/TD]
[TD]AF-001[/TD]
[TD="align: right"]02-04-2018[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]JOHN[/TD]
[TD="align: right"]20[/TD]
[TD]AF-042[/TD]
[TD="align: right"]14-04-2018[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]JOHN[/TD]
[TD="align: right"]20[/TD]
[TD]AF-050[/TD]
[TD="align: right"]29-04-2018[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]JOHN[/TD]
[TD="align: right"]20[/TD]
[TD]AF-201[/TD]
[TD="align: right"]20-06-2018[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]14[/TD]
[/TR]
</tbody>[/TABLE]
Target: Cells K2 to J4
[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]TARGET[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]MONTH[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD]QTR[/TD]
[TD="align: right"]15000[/TD]
[/TR]
</tbody>[/TABLE]
Calculation Desired result: Cells N1 to P3
[TABLE="width: 220"]
<tbody>[TR]
[TD]COMMISSION[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]Jun-18[/TD]
[TD]QTR-1[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]220[/TD]
[/TR]
</tbody>[/TABLE]
Logics: If he achieves Monthly & qtrly target, then eligible for commission, but the invoices which full payment received within 15 days that will be in calculation. In case of monthly commission it will be 5% of bill value & for qtr that will be 2%.
N3, O3, & P3 are my desired result. Pls help me with formulas month wise & qtr wise.
Pls note monthly & qtr achievement will separately be accepted.
Thanks in advance.
Regards
Ramu
Last edited: