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: