Calculation total not to exceed a specified number.
 

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

Calculation total not to exceed a specified number.
 

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" wrote in message ...
|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
|
|

Calculation total not to exceed a specified number.
 

If the sale is in A1 then:

=A1+MIN(A1*0.0225,36)+0.07*A1+IF(A11600,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

Calculation total not to exceed a specified number.
 

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(A11600,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

Calculation total not to exceed a specified number.
 

=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" wrote in message ...
| 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(A11600,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
|
|