Help / Support on complex IF Formula with multiple conditions

i5Rosa 

You can maybe use a nested formulas to solve this problem. One alternative is to use a combination of “IF” and “MIN” functions to calculate the commission for each band separately and then sum them up.

Here's an example:

=MIN(Monthly_Revenue, 0.8 * Annual_Target - Accumulative_Annual_Revenue) * 0.005

+MAX(0, MIN(Monthly_Revenue - MAX(0, 0.8 * Annual_Target - Accumulative_Annual_Revenue), Annual_Target - Accumulative_Annual_Revenue)) * 0.01

+MAX(0, MIN(Monthly_Revenue - MAX(0, Annual_Target - Accumulative_Annual_Revenue), 3000000 - Accumulative_Annual_Revenue)) * 0.015

+MAX(0, MIN(Monthly_Revenue - MAX(0, 3000000 - Accumulative_Annual_Revenue), 4000000 - Accumulative_Annual_Revenue)) * 0.02

+MAX(0, Monthly_Revenue - MAX(0, 4000000 - Accumulative_Annual_Revenue)) * 0.025

This formula calculates the commission for each band separately and then adds them up to get the total commission. You can replace “Accumulative_Annual_Revenue”, “Monthly_Revenue”, and “Annual_Target” with the appropriate cell references in your Excel sheet.

Formula untested, please test.