Dear Friends,
Calculating Commission:
Main sheet: Cells A1 to H5
<tbody>
</tbody>
Target: Cells K2 to J4
<tbody>
</tbody>
Calculation Desired result: Cells N1 to P3
<tbody>
</tbody>
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
SALESMAN | SALESMAN NO | BILL NO | BILL DATE | SOLD AMOUNT | PAYMENT RCVD | BALANCE | DAYS |
JOHN | 20 | AF-001 | 02-04-2018 | 1000 | 1000 | 0 | 10 |
JOHN | 20 | AF-042 | 14-04-2018 | 2000 | 2000 | 0 | 20 |
JOHN | 20 | AF-050 | 29-04-2018 | 3000 | 2500 | 500 | 12 |
JOHN | 20 | AF-201 | 20-06-2018 | 10000 | 10000 | 0 | 14 |
<tbody>
</tbody>
Target: Cells K2 to J4
TARGET | |
MONTH | 5000 |
QTR | 15000 |
<tbody>
</tbody>
Calculation Desired result: Cells N1 to P3
COMMISSION | ||
Apr-18 | Jun-18 | QTR-1 |
50 | 500 | 220 |
<tbody>
</tbody>
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: