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

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

"Carjocky" <(E-Mail Removed)> wrote in message news:953E929E-7118-48E9-BC68-(E-Mail Removed)...
|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
|
|

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

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

=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

"Carjocky" <(E-Mail Removed)> wrote in message news:37C23859-2E4F-40A3-90D2-(E-Mail Removed)...
| 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
| > >
| > >