Hello, I am a novice at writing formulas for excell, but did get this to
work for me to a point. Here goes. I want to write a formula that will
calculate how much federal tax to withold based on a given monetary
amount.
Here is how the tax code says to figure it......If the amount of
wages is not over $248, then the amount of tax to withhold is 0. If the
amount of wages is over $248, but less than $710 then the amount of tax
to withold is 10% of the excess over $248. If the amount of wages is
over $710, but less than $2013 then the amount of tax to withhold is
$46.20 plus 15% of the amount in excess of $710.
Now, here is what I wrote.
=IF(AND(A1>248,A1<=710),.1*(A1-248))
This formula works fine, so long as the wages are not over 710, my
question is how do I use another formula with different wage amounts in
the same cell, and have excell pick which formula it needs to use based
on the wage amount in cell A1.

I hope someone can give me some guidance on this, and hopefully my
message is not too confusing. Thank You!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Hi

=IF(AND(A1>248,0.1*(IF(A1>710,710,A1)-248),0)+IF(A1>710,0.15*(IF(A1>2013,201
3,A1)-710),0))



--
(When sending e-mail, use address (E-Mail Removed))
Arvi Laanemets



"fyrfytr265" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Hello, I am a novice at writing formulas for excell, but did get this to
> work for me to a point. Here goes. I want to write a formula that will
> calculate how much federal tax to withold based on a given monetary
> amount.
> Here is how the tax code says to figure it......If the amount of
> wages is not over $248, then the amount of tax to withhold is 0. If the
> amount of wages is over $248, but less than $710 then the amount of tax
> to withold is 10% of the excess over $248. If the amount of wages is
> over $710, but less than $2013 then the amount of tax to withhold is
> $46.20 plus 15% of the amount in excess of $710.
> Now, here is what I wrote.
> =IF(AND(A1>248,A1<=710),.1*(A1-248))
> This formula works fine, so long as the wages are not over 710, my
> question is how do I use another formula with different wage amounts in
> the same cell, and have excell pick which formula it needs to use based
> on the wage amount in cell A1.
>
> I hope someone can give me some guidance on this, and hopefully my
> message is not too confusing. Thank You!
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>

I tried the formula and it gave me the message that there were too many
argument. Any thoughts?


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Take a look here:

http://www.mcgimpsey.com/excel/taxvariablerate.html

In article <(E-Mail Removed)>,
fyrfytr265 <(E-Mail Removed)> wrote:

> Hello, I am a novice at writing formulas for excell, but did get this to
> work for me to a point. Here goes. I want to write a formula that will
> calculate how much federal tax to withold based on a given monetary
> amount.
> Here is how the tax code says to figure it......If the amount of
> wages is not over $248, then the amount of tax to withhold is 0. If the
> amount of wages is over $248, but less than $710 then the amount of tax
> to withold is 10% of the excess over $248. If the amount of wages is
> over $710, but less than $2013 then the amount of tax to withhold is
> $46.20 plus 15% of the amount in excess of $710.
> Now, here is what I wrote.
> =IF(AND(A1>248,A1<=710),.1*(A1-248))
> This formula works fine, so long as the wages are not over 710, my
> question is how do I use another formula with different wage amounts in
> the same cell, and have excell pick which formula it needs to use based
> on the wage amount in cell A1.
>
> I hope someone can give me some guidance on this, and hopefully my
> message is not too confusing. Thank You!
>

Hi

There was a bracket too much at end - sorry but I was in hurry and couldn't
test the formula.
=IF(A1>248,0.1*(IF(A1>710,710,A1)-248),0)+IF(A1>710,0.15*(IF(A1>2013,2013,A1
)-710),0)


--
(When sending e-mail, use address (E-Mail Removed))
Arvi Laanemets


"fyrfytr265" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> I tried the formula and it gave me the message that there were too many
> argument. Any thoughts?
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>

Thanks for the help. I figured it out on my own but thought I would post
my solution to see if anyone knows of a better way to write it.

Some of the figures are different, I was using outdated tax figures,
but the concept is the same.

In the formula, F27 represents gross wages. If gross is less than or
equal to 308 then tax is 0. If gross is between 308 and 858 then tax is
10% of the amount of wages over 308. If gross is between 858 and 2490
then tax is $55 plus 15% of amount of wages over 858.

=IF(F27<=308,0,IF(AND(F27>308,F27<=858),0.1*(F27-308),IF(OR(F27>858,F27<=2490),55+0.15*(F27-858),(0))))

If anyone knows of another way please feel post it!

Again, Thanks.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

I'm no expert, but it seems to me that writing a formula that incorporates
the tax scales is counter productive. Each time the scales or thresholds
change you have to meticulously edit the formula.

Why not set the tax data up in a table and use the LOOKUP function. Future
changes can be made to the table without editing the formula.

--
Ken Russell


"fyrfytr265" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
|
| Thanks for the help. I figured it out on my own but thought I would post
| my solution to see if anyone knows of a better way to write it.
|
| Some of the figures are different, I was using outdated tax figures,
| but the concept is the same.
|
| In the formula, F27 represents gross wages. If gross is less than or
| equal to 308 then tax is 0. If gross is between 308 and 858 then tax is
| 10% of the amount of wages over 308. If gross is between 858 and 2490
| then tax is $55 plus 15% of amount of wages over 858.
|
|
=IF(F27<=308,0,IF(AND(F27>308,F27<=858),0.1*(F27-308),IF(OR(F27>858,F27<=249
0),55+0.15*(F27-858),(0))))
|
| If anyone knows of another way please feel post it!
|
| Again, Thanks.
|
|
| ------------------------------------------------
| ~~ Message posted from http://www.ExcelTip.com/
| ~~View and post usenet messages directly from http://www.ExcelForum.com/
|

I weighed the options and decided the formula was the best way to go
since I only have to change 8 numbers when the tax scale changes. If I
used a table then there would be hundreds if not thousands of numbers
to change when the tax rate changes. Also, I can use the same formula
for both married and single employees by changing the previously
mentioned 8 numbers, whereas using tables, I would have to create two
different tables with hundreds or thousands of numbers.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

I'm afraid I didn't make myself clear. The table you set up would contain
exactly the data you gave us in your post, not the whole tax schedule.

The LOOKUP formula simply accesses this very short table and does its
calculations based on the thresholds in the table.

Ken Russell

"fyrfytr265" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
|
| I weighed the options and decided the formula was the best way to go
| since I only have to change 8 numbers when the tax scale changes. If I
| used a table then there would be hundreds if not thousands of numbers
| to change when the tax rate changes. Also, I can use the same formula
| for both married and single employees by changing the previously
| mentioned 8 numbers, whereas using tables, I would have to create two
| different tables with hundreds or thousands of numbers.
|
|
| ------------------------------------------------
| ~~ Message posted from http://www.ExcelTip.com/
| ~~View and post usenet messages directly from http://www.ExcelForum.com/
|

Hi

As this tax scale table will be edited only occassionally, you can define it
with series of named ranges instead - as bonus you formulas will be nicer to
look at too.
An example with my formula here (I added parts for 0-tax to formula, to get
calculating fully formalized in my example, and also edited it a bit).
The OP has determined 3 tax ranges:
The part of gross between >0 and <=308 is taxed with 0%
The part of gross between >308 and <=858 is taxed with 10%
The part of gross between >858 and <=2490 is taxed with 15%
The part of gross >2490 isn't given, let's be 0%

Defined named ranges will be p.e.:
Lim1=308
Lim2=858
Lim3=2490
Tax1=0
Tax2=0.1
Tax3=0.15
Tax4=0

The general formula will be
=Tax1*IF(A1>Lim1,Lim1,A1)+Tax2*IF(A1>Lim1,IF(A1>Lim2,Lim2,A1)-Lim1,0)+Tax3*I
F(A1>Lim2,IF(A1>Lim3,Lim3;A1)-Lim2,0)+Tax4*IF(A1>Lim3,A1-Lim3,0)

It's very easy to expand, when additional ranges are added to tax scale -
add named ranges and according number of parts to formula, and edit the last
part of formula.
As about different scales for married and single employees - I'm confused.
Maybe singles have some additional tax, which can be calculated separately?

For OP, we can drop parts of formula with Tax=0
=Tax2*IF(A1>Lim1,IF(A1>Lim2,Lim2,A1)-Lim1,0)+Tax3*IF(A1>Lim2,IF(A1>Lim3,Lim3
;A1)-Lim2,0)


--
(When sending e-mail, use address (E-Mail Removed))
Arvi Laanemets


"Ken Russell" <(E-Mail Removed)> wrote in message
news:3fa9bc91$0$3502$(E-Mail Removed)...
> I'm afraid I didn't make myself clear. The table you set up would contain
> exactly the data you gave us in your post, not the whole tax schedule.
>
> The LOOKUP formula simply accesses this very short table and does its
> calculations based on the thresholds in the table.
>