Forum Discussion
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
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.
I really appreciate your assistance
thank you HansVogelaar