Dec 31 2022 03:56 PM
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
Jan 01 2023 02:24 PM
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.
Jan 01 2023 05:06 PM
I really appreciate your assistance
thank you @HansVogelaar
Jan 06 2023 11:52 AM
Jan 06 2023 12:28 PM
The first five added together come to 256,666, not 266,666
Jan 06 2023 12:33 PM
Jan 06 2023 12:46 PM - edited Jan 06 2023 01:10 PM
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.
Jan 06 2023 01:26 PM
so it's exactly the same as in my original reply, but with an expanded lookup table.
Jan 07 2023 03:50 PM
Jan 08 2023 07:14 AM
That is because you provided incorrect information, as you mention yourself. You wrote "first 20,000 7% Next 20,000 11%" but now it turns out to be 25000 instead 20000.