Forum Discussion

Mohamed Nour's avatar
Mohamed Nour
Copper Contributor
Jan 04, 2018

Multiple IF function

Dears

 

I need your help to create an IF formula to the following table

                           
Achieve number of collected invoices per Quarter   200 Invoice % applied No. Of Invoices collected Target % Achieved % Applied
          90-100% from target invoices 100%     200 0%  formula to be added
          80-89% from target invoices 80%          
          70-79% from target invoices 70%          
          50-69% from target invoices 50%          
          25-49% from target invoices 25%          
          > 25%     0%          

 

i need to insert an if formula to add the % applied based on the conditions set on the left side ...for example if he collect 180 invoice out of 200 invoice so he achieve 90% so we apply 100% commission

 

please support as I'm confused with this formula

Formulas & Functions
Need Help

5 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 04, 2018

    Hi Mohamed,

     

    Better to create helper table with ranges for your commissions. It could be as a range or even better as Excel table (see on the top here)

    When the formula to calculate commission will be

    =LOOKUP(B10,$B$2:$B$7,$C$2:$C$7)

    in first case or

    =LOOKUP(E10,PerCent[Collected],PerCent[Commission])

    for the table (at the bottom of the screenshot), and in attached file

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jan 04, 2018

        I moved the ranges here

        The formula with it is

        =LOOKUP(N6,$F$24:$F$29,$G$24:$G$29)

        And in attached file.

         

        In your initial logic is not clear what to do if the % of collected invoice is between boundaries, e.g. 49.5% (you have ranges 25-49% and 50-69%). In my ranges if less than 50% is one percent, if more or equal to 50% is another one. But that could be changed by selecting proper boundaries.

Resources