| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 | | |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 | | |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I want to auto-sum and not exceed a certain number | Excel Discussion (Misc queries) | |||
| Number of total calculation functions | Excel Worksheet Functions | |||
| Percentage total may not exceed 100 | Excel Discussion (Misc queries) | |||
| The number of hours in a day cannot exceed 24... | Excel Worksheet Functions | |||
| How do I enter a not to exceed number 1 in a formula? | Excel Programming | |||
Linear Mode
