# 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!

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

Question marked as
Solved

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

Regards,

Barry

Question marked as
★
Helpful

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

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

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

Question marked as
★
Helpful

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

User profile for user: Chedonna

Question: Conditional formulas