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

Question:

# Question:Q: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

Question marked as Solved

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

Regards,

Barry

Posted on

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

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

Also a conditional formula for: if E31 is greater than 9325, multiply the value of (E31-9325)*.15.

Dec 10, 2017 7:53 PM

Question marked as Solved

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

Regards,

Barry

Dec 10, 2017 8:39 PM

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

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

Thanks Barry and SG. Exactly what I needed. The last time I did conditional formulas was in Excel on my first PC with windows 😕 so I greatly appreciate the help.

Dec 18, 2017 7:30 PM

User profile for user: Chedonna

Question: Conditional formulas