Calculation total not to exceed a specified number.

[Guest]

I am trying to set up 2 calculations for our sales tax.

(1) The local tax is 2.25% of the sale up to the first $1600 of the sale or
a maximum local tax of $36. If the sale is $3000 the tax is still $36. If
the sale is less than $1600 then the tax is 2.25% of that amount.

(2) There is a state tax of 7% on the total sale. There is an additional
state tax of 2.75% (single item purchase tax) on the amount of the sale
between $1600 and $3200 or a maximum of $44.

So if you have a sale of $10,000 there is a state tax of 7% on the total
($700), local tax of $36 plus a state single item purchase tax of $44.

A formula for this will be greatly appreciated!

Jim

 

[Niek Otten]

Hi Jim,

The total is:

=A1+MIN(A1*2.25%,36)+A1*7%+MAX(0,MIN(44,A1*2.75%))

The "+" signs separate the individual taxes
--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am trying to set up 2 calculations for our sales tax.
|
| (1) The local tax is 2.25% of the sale up to the first $1600 of the sale or
| a maximum local tax of $36. If the sale is $3000 the tax is still $36. If
| the sale is less than $1600 then the tax is 2.25% of that amount.
|
| (2) There is a state tax of 7% on the total sale. There is an additional
| state tax of 2.75% (single item purchase tax) on the amount of the sale
| between $1600 and $3200 or a maximum of $44.
|
| So if you have a sale of $10,000 there is a state tax of 7% on the total
| ($700), local tax of $36 plus a state single item purchase tax of $44.
|
| A formula for this will be greatly appreciated!
|
| Jim
|
|

 

[Guest]

If the sale is in A1 then:

=A1+MIN(A1*0.0225,36)+0.07*A1+IF(A1>1600,MIN(44,0.0275*A1),0)

round up or down to the nearest penny as the law requires

 

[Guest]

Thank you for the response. I need the tax amounts in to show in their own
cells and to reflect only the tax. I tried to figure out how to do that
without bothering you again but no luck. Not very good with this kind of
thing.

Jim

 

[Niek Otten]

=MIN(A1*2.25%,36)
=A1*7%
=MAX(0,MIN(44,A1*2.75%))

That's what I meant when I wrote that the individual taxes were separated by + signs

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Thank you for the response. I need the tax amounts in to show in their own
| cells and to reflect only the tax. I tried to figure out how to do that
| without bothering you again but no luck. Not very good with this kind of
| thing.
|
| Jim
|
| "Gary''s Student" wrote:
|
| > If the sale is in A1 then:
| >
| > =A1+MIN(A1*0.0225,36)+0.07*A1+IF(A1>1600,MIN(44,0.0275*A1),0)
| >
| > round up or down to the nearest penny as the law requires
| > --
| > Gary's Student
| >
| >
| > "Carjocky" wrote:
| >
| > > I am trying to set up 2 calculations for our sales tax.
| > >
| > > (1) The local tax is 2.25% of the sale up to the first $1600 of the sale or
| > > a maximum local tax of $36. If the sale is $3000 the tax is still $36. If
| > > the sale is less than $1600 then the tax is 2.25% of that amount.
| > >
| > > (2) There is a state tax of 7% on the total sale. There is an additional
| > > state tax of 2.75% (single item purchase tax) on the amount of the sale
| > > between $1600 and $3200 or a maximum of $44.
| > >
| > > So if you have a sale of $10,000 there is a state tax of 7% on the total
| > > ($700), local tax of $36 plus a state single item purchase tax of $44.
| > >
| > > A formula for this will be greatly appreciated!
| > >
| > > Jim
| > >
| > >