Forum Discussion

Habib_Taan's avatar
Habib_Taan
Copper Contributor
Dec 31, 2022

I need a formula for this

the amount is 100,000. i want 7% from first 30,000, and 11% from second 30,000 and 15% from third 30,000

how can i write a formula that will always work even when the amount is lower than 100,000

10 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 01, 2023

    Habib_Taan 

    I have moved this from the Community Lounge to the Excel forum.

    You don't specify what should happen with the last 10,000. In the following, I have assumed that the rate is 19%, but you can easily change that.

    I created a rate table in D2:F5.

    The formula in B2 is

    =SUMPRODUCT((A2>$D$2:$D$5)*(A2-$D$2:$D$5),$F$2:$F$5)

    If you have Microsoft 365 or Office 2021, you can use

    =SUM((A2>$D$2:$D$5)*(A2-$D$2:$D$5)*$F$2:$F$5)

    See the attached demo.

    • Habib_Taan's avatar
      Habib_Taan
      Copper Contributor
      Jan 06, 2023
      Hello, Can you assist again in generating formula for the below scenario 



      first 20,000 7%

      Next 20,000 11%

      Next 41,666 15%

      Next 41,666 19%

      Next 133,333 21%

      Above 266,666 24%



      Again, thanks for the help
      • mtarler's avatar
        mtarler
        Silver Contributor
        Jan 06, 2023

        what about:

         

        =A1*0.07 + MAX(0, (A1-20000)*0.04) + MAX(0, (A1-40000)*0.04) + MAX(0, (A1-81666)*0.04) + MAX(0, (A1-123332)*0.02) + MAX(0, (A1-266666)*0.03)

         

        Basically this adds the additional % to the amounts over each threshold.

        alternately 

         

        =MMULT(IF(A1>{0,20000,40000,81666,123332,266666},A1-{0,20000,40000,81666,123332,266666},0),{0.07;0.04;0.04;0.04;0.02;0.03})

         

        or

         

        =LET(in,A1,
  thresh,{0,20000,40000,81666,123332,266666},
  rateInc,{0.07;0.04;0.04;0.04;0.02;0.03},
  MMULT(IF(in>thresh,in-thresh,0),rateInc))

         

        Looking back at HansVogelaar  prior answer these are nearly identical in concept and it is better to have those values in a table on your sheet so you can easily see it and change it when needed so I recommend his version.

         

Resources