Any Microsoft Excel experts here?

[gtp1976]

I have a question. Can you do if/then scenarios in an individual cell of an Excel spreadsheet? If so, how?

What I'm wanting to do is create a spreadsheet for my employees payroll withholdings for their taxes. They do not have a set weekly salary so every week, I have to consult the book to determine Federal withholdings. I have a formula for FICA, State, Local, Medicare, etc, but Federal floats. Could I put in something like If the value of Cell X is between this and this, then enter this amount here. If it is between this and this, then enter this amount.

I know that would be one hell of a long string to type in, but it would be well worth it if I could.

[Renew Deal]

1. You can probably do it in Access

Access may even have a template for that.

[Blue-Jay]

2. Yes I believe you can.

[lazarus]

3. try something like

=IF(A2<x,"y",IF(a2>x,"z")

Where x is one amount, and y is one type of FICA, z another. This says that if the field is less than x, enter y, if it's more than x, enter z. You can manipulate it from there.

[immoderate]

5. That will give you an error.

See syntax in post below.

--IMM

[lazarus]

8. I pulled that example

straight out of Excel help. :shrug:

Oh, I see, I forgot the final close parentheses. should be

=IF(A2<x,"y",IF(a2>x,"z"))

[immoderate]

9. That is better, but...

There is room for another argument, which I think is required. What to do if x=A2, meaning the result of the second IF is FALSE. Still looks like an error.

--IMM

[lazarus]

13. I just checked it out on my excel

It works for what it does. One could add another argument for equals, i guess. Depends on how elegant you want it. :-)

[immoderate]

22. I'm a little surprised.

Arguments in bold are supposed to be mandatory. I thought all IF statements required three arguments. Well not to assume, I guess.

BTW, George Bush is an idiot. (Oops that just crept out.)

--IMM

[immoderate]

4. Use the IF function.

The syntax is:

IF(logical test, value if TRUE, value if FALSE)

Using Paste Function will make it easier. Also be aware of the AND function if you want to create a compound test. Here's an example:

=IF(AND(A1>10,A1<20),A1*.05,A1*.02)

This formula looks at value in cell A1. If it is between 10 and 20 it multiplies by 5%, otherwise it multiplies by 2%.

Hope that helps.

On edit: I want to add that it is not good practice to use numbers in formulas (as I did in the example above.) Rather, those percentages should be in cells, and referenced (absolute). Many reasons for this, such as, easier to print, update, audit, etc.

--IMM

[gtp1976]

6. would something like this work?

=IF(AND(A1>10,A1<20),B1=5),(and(a1>20,a1<30),B1=6),(and(a1>30,a1<40),B1=7)) etc.

Also, is there a greater than or equal to/less than or equal to function in Excel?

[immoderate]

7. Sort of.

You can "nest" ANDs, ORs, and IFs. Here's an example:

=IF(AND(AND(A1>=10,A1<=20),B1=5),A1*.05,"N/A")

In this one, if the value of A1 is greater than of equal to 10, and less than or equal to 20, and the value in B1 is equal to 5, it will return (display) A1 times 5%, otherwise it will show the text message N/A. Don't forget to use quotes for text entries.

If you describe what you want to do, I could help you write it.

--IMM

[gtp1976]

12. here's what I am trying to do

I have a basic spreadsheet that looks something like this

(Date) (Gross Pay) (Fed With) (State With) (Local) (FICA) (Med) (Net Pay)

I know how to calculate everything except for Federal because they are simple percentages. FICA is =sum(B1*.062) That automatically calculates the FICA because no matter what I enter for Gross Pay, it just takes 6.2% and enters it in to this cell. For Net Pay I just use =sum(B1-C1-D1-E1-F1-G1) and this takes the gross, subtracts all the taxes and gives me the net. I suppose it would be less typing to make it =sum(B1-sum(C1:G1)) but whatever. :-)

The Federal withholdings is not based on any percentage. The federal withholding on $440-450 for a married person is $30. It is not proportional, though. The Federal withholding for $880-890 is $96. More than 3 times that of $440-450.

Knowing a general range of what they make (it floats but it is usually in a similar range each week), is there any way to tell Excel to put $X in to the Fed With Cell based upon what I type in to the Gross Pay cell?

[immoderate]

17. There's ALWAYS a way...

Three are discussed in this thread. A complex formula, a custom function (thoughtfully provided by Thor) and a lookup table. Like all other computer stuff, it's easy when you know how (as in you'll never pull a muscle.)

BTW, (the teacher in me wants me to say) you could make your formula simpler, =B1-sum(C1:G1)

I think there is a limit on nested IF statements, 7 deep, and if the function is like Thor's, a lookup might be better. The VBA solution is elegant, but it's not usually the first place I would point people who haven't been there. But with some help, that would be cool.

Here's a format for nested IFs.

=IF(Salary>=Step1,Salary*0,IF(Salary>=Step2,Salary*Pct1,IF(Salary>=Step3,Salary*Pct3,IF...

If the number is LTOET Step 1 it multiplies by zero, if not it checks to see if it is in step 2, and so on. The numbers should be in a table of assumptions on the side, and called by absolute references (i.e. $A$1) so the formulas can be copied without screwing up references. Using range names makes this more elegant while opening another can of worms. (Using range names you could use exactly the formula above.)


--IMM

[gtp1976]

18. The problem is...

I'm not an excel expert so I don't really understand what I'm reading. I do appreciate the help, but until I get more knowledge of Excel I'll probably have to do what I'm doing now and just look it up in the book. However, I could easily setup a table in a seperate worksheet that has the values of each withholding for each gross pay range. Would it then be fairly easy to have Excel look it up from that table?

[Thor_MN]

10. I would do a custom function in a module

Tools, Macro, Visual Basic Editor.

Insert, Moudule.

in the moudule paste


Function FedWithold(cSalary As Currency)
Select Case cSalary
Case Is < 2650
FedWithold = 0
Case Is < 9700
FedWithold = (cSalary - 2650) * 0.1
Case Is < 30800
FedWithold = (cSalary - 9700) * 0.15 + 705
Case Is < 68500
FedWithold = (cSalary - 30800) * 0.25 + 3870
Case Is < 148700
FedWithold = (cSalary - 68500) * 0.28 + 13295
Case Is < 321200
FedWithold = (cSalary - 148700) * 0.33 + 35751
Case Else
FedWithold = (cSalary - 321200) * 0.35 + 92676
End Select
End Function



In your sheet, you can then use your FedWithold function by setting a cell to

=FedWithold( RC<-1> )

to get the witholding for the salary for the cell to the right of the calculated cell. The < and > in the cell reference should be square brackets, but I don't know how to force the HTML to ignore them...

That should give you enough to get started using your own custom functions with all the capabilities of VBA at your fingertips.

[immoderate]

11. This is nice but...

Those formulas have to be adjusted for weekly input, I think. Presents some other problems, I think. Nice code, though.

On edit: Appreciate your reminding me of the idiosyncrasies of VBA in the Case statement. Thanks for the lesson. Seeing this reminds me that another approach for the withholding problem would be a LOOKUP table.

--IMM

[Thor_MN]

14. I don't understand.

You feed the function the salary, it hands you back the witholding...

I thought that was what you were trying to do...

If the example I gave you is not right, modify the code to give what you want, it's a lot easier than debugging 8 nested if functions in a single cell....

If nothing else, put your if functions in the module so they are easier to read...

[gtp1976]

15. see post 12 above to see what I was trying to do.

nt

[Thor_MN]

16. OK, my example has the 2004 single rates for annual amounts,

but you could do

=FedWithold(RC<-1>*52)/52

Or just modify the select case to fit the 2005 rates numbers on a weekly basis.


You could also use the VLOOKUP function to find the cell in a table of the salary and witholding amounts. In that case, you would want to have the range_lookup parameter as true, so that you get an approximate lookup. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned.

[gtp1976]

19. I think we may be on to something here...

If I did a seperate worksheet with the ranges and the amounts withheld from easch, what would be the formula for the lookup? I have a limited knowledge of Excel so if you could, please speak to me as if I am a 6th grader. :-)

[immoderate]

21. About lookup.

Well I'm a 6th grader when it comes to figuring taxes, but you could set up a table in a seperate sheet something like this:

Salary  Pct  Exemption  FICA(?)
1000    0    0          0
5000    .1   500        20
10000   .5   2000       300

I'm not sure which numbers you would need for your formula so I'm making up a simple case. Let's say this table (data only, not headings) is in range J2:M4. And the salary is in A1.

The syntax is:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)


The expression for the percent would be would be:

=VLOOKUP(A1,$J$2:$M$4,2,True)

Where A1 is the salary, $J$2:$M$4 is the range of the lookup table. (The dollar signs make the reference absolute. That means it won't change when you copy the formula. By contrast A1 is a relative reference. It will become B1 when copied to the line below.) The 2 is the column of the ref table that you want the number returned from, in this case the percentage. The "True" means it doesn't have to match the value in column 1 exactly, and will return data for intermediate values. It's optional here.

The full formula that will subtract the exemption from the salary, multiply the result by the percentage, and add what I called FICA in this table, is this: (Remember, I don't understand taxes.)

=(A1-VLOOKUP(A1,$J$2:$M$4,3))*VLOOKUP(A1,$J$2:$M$4,2)+VLOOKUP(A1,$J$2:$M$4,4)

(On Edit: Fixed this formula.)

I left out the range_lookup parameter because it defaults to TRUE if omitted.

If you copy this formula down the A1 changes to B1, C1, etc. the lookup table ref does not.

If you want to put the lookup table on another sheet you must include the sheet name and an exclamation point in the table reference. For example:
VLOOKUP(A1,Sheet2!$J$2:$M$4,3)

Does that make sense?

--IMM

[gtp1976]

23. Not a percentage nor FICA...

FICA is Social Security and is 12.4% of Gross if you are self employed or 6.2% of gross if your taxes are withheld by your employer. You pay 6.2% and your employer matches that 6.2% to equal 12.4%.

This is (I guess) your Federal Income tax. I guess it is a percentage, but it is not the same for all income levels and there seems to be no rhyme or reason for how it fluctuates. The example I gave above are the only two numbers I know off the top of my head. Gross pay = $440-450 per week...Federal/Income Tax withholding is $30. Gross pay = $880-890 per week...Federal/Income Tax withholding is $96.

Basically, I have a book/table from the IRS that gives me what the Federal/Income Tax withholding is for each Gross Pay. It is all in dollars. What I would have to do is set up an Excel sheet that basically reproduces this table. It would look something like this:

440-449 (next cell) 30
450-459 (next cell) 31
460-469 (next cell) 32
470-479 (next cell) 35
480-489 (next cell) 37

No pattern to follow.

If I did this, how might I format the Fed/Income Tax withholding cell to reflect this?

[immoderate]

24. The table looks like...

this. That is, the lookup table. The first column can be though of as having the "up to..." value. The values must be in ascending order. My guess is that the tax tables are based on some sort of formula, which should be the same for all in the same bracket, etc. though I don't know it. Your lookup table would look like this:

449     30
459     31
469     32              etc.

The VLOOKUP would return data from col 2 of the lookup table.

Here's another thought. I would be surprised if these tables didn't already exist on the net is some form that could be easily copied to a worksheet. Try irs.gov.

--IMM

[gtp1976]

25. humor me for a moment

Lets just use the example you provided. Worksheet is called, "Federal Table" and has only the 3 examples you gave.

A1=449 A2=30
B1=459 B2=31
C1=469 C2=32

Assuming my employee made $459 (gross) that week, what formula would I use to get the $31 to appear in that cell?

[immoderate]

26. Lets say the lookup ref $459 is in A1 on sheet 1

Are you sure you want the table to go ACROSS like that? Then you would use HLOOKUP (horizontal).

But I'm taking the liberty to transpose what you said. Say that col of salaries is A, and the tax is B.

Then your lookup range is: Federal Table!A1:B3


Then in b1 you type:

=VLOOKUP(A1,'Federal Table'!$A$1:$B$3,2)

On Edit: Excel puts single quotes around the sheet name, because it has a space in it. I usually don't put spaces in sheet names. It's allowed, but can be awkward.

That goes down col 1 of the lookup range until it finds 459, and returns the value in col 2 of that table.

--IMM

[gtp1976]

27. okay, so far so good

Now, how would I create the table to have a range of numbers rather than 449 = x 450 = x 451 = x etc. How would I enter 450-459 = x 460-469 = y 470-479 = z etc.

[immoderate]

28. That's what we're doing.

You just list the upper boundary of each range in the first column of the lookup table in ascending order. If the columns look like this:

459 xxxx
469 yyyy
479 zzzz

For salaries 459 or less, it will read data from the first row. If it's above 459, it drops down. If it's between 459 and 469, it reads data from the second row. If it's above 469, it continues to look down the list.

You can't have more than one number in a cell. An entry like 459-469 is read as text by Excel. Or it looks like a subtraction formula without the equals sign.

You would have to put your upper and lower limit in two different cells, and we do. Except the range boundaries are vertical. The lookup table as a whole does what you're asking.

On edit: If it helps, think of the lookup table as working like a multi-layered sieve.

--IMM

[gtp1976]

29. Excellent. Thank You

Very much for your help. A lot of people get frustrated when they have to explain things to someone else, but you have done an excellent job. :-)

[immoderate]

30. If it works, I share your triumph!

If you get into trouble, PM me. Glad to be helpful.

--IMM

[gtp1976]

31. It worked. Thanks again. n/t

[immoderate]

20. I am admiring your approach.

I like the VBA approach. Just thought that sending the uninitiated to the VBA editor would be a bit daunting.

I think I agree that a lookup table would be better than a humongous nested formula. And nested IFs is limited to seven.


Good point about range_lookup paramater. If omitted though, it defaults to TRUE anyway.

--IMM