Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

Question: Conditional formulas

I need a conditional formula for my tax calculations. Essentially: Multiple the value of a cell only up to 9325 by .10. Help!

MacBook Pro (13-inch Mid 2009), Mac OS X (10.7.5)

Posted on

Reply
Question marked as Solved
Answer:
Answer:

Both tiers can be joined in a single formula. If more than two tiers are involved, the formula gets complicated, and it's time to bring in a tax table/lookup table.


Here's a formula that calculates taxes accoring to the rules you have presented. The amounts to be taxed are in Column A.


MIN(A2,9325)×0.1+MAX(A2−9325,0)×0.15

User uploaded file


Regards,

Barry

Posted on

Question marked as Helpful

Dec 18, 2017 7:29 PM in response to Chedonna In response to Chedonna

One way to do this kind of "tiered" or "bracket" calculation is like this:


User uploaded file


The formula in F31, filled down, is:


=MAX(0,(MIN(9325,E31)−0))*0.1+MAX(0,(MIN(15000,E31)−9325))*0.15


This looks complicated, but it's just a repetition of the same pattern for each bracket.


If you have more brackets then you simply expand this formula by adding another + and the same pattern. The pattern for each term after the + is MAX(0,(MIN(<upper end of bracket>,E31)−<lower end of bracket>))*<rate for that bracket>. That way you can have many brackets in one easily edited formula without resorting to a lookup table.


The figures in red in the screenshot are the top end of each bracket, and green the lower end.


Here I assumed arbitrarily that 15000 was the upper end of the second bracket. You would want to replace that with the actual value.


SG

There’s more to the conversation

Read all replies
Question marked as Solved

Dec 10, 2017 8:39 PM in response to Chedonna In response to Chedonna

Both tiers can be joined in a single formula. If more than two tiers are involved, the formula gets complicated, and it's time to bring in a tax table/lookup table.


Here's a formula that calculates taxes accoring to the rules you have presented. The amounts to be taxed are in Column A.


MIN(A2,9325)×0.1+MAX(A2−9325,0)×0.15

User uploaded file


Regards,

Barry

Dec 10, 2017 8:39 PM

Reply Helpful
Question marked as Helpful

Dec 18, 2017 7:29 PM in response to Chedonna In response to Chedonna

One way to do this kind of "tiered" or "bracket" calculation is like this:


User uploaded file


The formula in F31, filled down, is:


=MAX(0,(MIN(9325,E31)−0))*0.1+MAX(0,(MIN(15000,E31)−9325))*0.15


This looks complicated, but it's just a repetition of the same pattern for each bracket.


If you have more brackets then you simply expand this formula by adding another + and the same pattern. The pattern for each term after the + is MAX(0,(MIN(<upper end of bracket>,E31)−<lower end of bracket>))*<rate for that bracket>. That way you can have many brackets in one easily edited formula without resorting to a lookup table.


The figures in red in the screenshot are the top end of each bracket, and green the lower end.


Here I assumed arbitrarily that 15000 was the upper end of the second bracket. You would want to replace that with the actual value.


SG

Dec 18, 2017 7:29 PM

Reply Helpful (1)
User profile for user: Chedonna

Question: Conditional formulas