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

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

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})

=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.

Forum Discussion

I need a formula for this

Resources