Forum Discussion

i5Rosa's avatar
i5Rosa
Copper Contributor
Apr 25, 2023

Help / Support on complex IF Formula with multiple conditions

Good afternoon, 

I was hoping to see if someone may be able to provide some support in creating a single/several IF formulas to calculate overall commissions. 

Annual Target = £2,350,000.00 

Base information for the commission structure is across 5 bands. 

Band 1 is 80% of Annual target = monthly revenue * 0.5% 
Band 2 is 80% to 100% of Annual target = monthly revenue * 1.0% 

Band 3 is 100% of annual target but under £3m = Monthly revenue * 1.5% 
Band 4 is £3m to £4m = monthly revenue * 2.0% 

Band 5 is £4m + = monthly revenue *2.5% 

However, if the monthly revenue amount causes the accumulative annual target to enter another band then the difference needs to be split between the two bands. 

Example: if the Accumulative annual revenue is £2,000,000 & the monthly revenue is £500,000. 

Then £380,000 is in Band 1 & £120,000 is in band 2. 

Hope I've explained that clearly. 

excel
Formulas and Functions

2 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Apr 25, 2023

    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.

Resources