Excel Formula Difficulties

[aja2150]

Can someone spot the error in this formula?

=IF(F14="Yes",(E14<=4)(30)(E14>4,(E14-4)*(5)+(30),IF(F14="no",0,""))

The formula is supposed to look at F14 and look up "yes" or "no". If the answer is yes then it looks at E14 and if the number is <=4, then it returns the value "30". If the number is >4 then it adds $5 per number over 4 and adds that to "30".

Any help is greatly appreciated!!

 

[njimack]

=if(f14="Yes",if(e14<=4,30,((e14-4)*5)+30),0)

 

[MrKowz]

Try:

=IF(F14="No",0,IF(F14="Yes",IF(E14<=4,30,((E14-4)*5)+30),"")

 

[aja2150]

Thanks a million! Worked perfectly!

 

[aja2150]

Could I get your expertise on the following formula?

=VLOOKUP(K2,'Federal Tax Table'!B5:I13,MATCH(E2,'Federal Tax Table'!C4:I4,0),TRUE)

K2=Gross Earnings
E2= Number of dependents

What I am trying to do is have the formula find the correct tax percentage based on gross earnings and number of dependents. The formula listed above keeps returning a value from the earnings column of the tax table instead of the tax rate.

I really appreciate the help!

 

[MrKowz]

It seems to me that you are needing an INDEX/MATCH formula, what you have there could have issues.

Can you please describe your data layout, and what logic/expected return is?

 

[T. Valko]

Could I get your expertise on the following formula?

=VLOOKUP(K2,'Federal Tax Table'!B5:I13,MATCH(E2,'Federal Tax Table'!C4:I4,0),TRUE)

K2=Gross Earnings
E2= Number of dependents

What I am trying to do is have the formula find the correct tax percentage based on gross earnings and number of dependents. The formula listed above keeps returning a value from the earnings column of the tax table instead of the tax rate.

I really appreciate the help!

Your column_index_number is always going to be off by 1.

Increase the range of the lookup_array argument in the MATCH function.

MATCH(E2,'Federal Tax Table'!C4:I4,0)

To:

MATCH(E2,'Federal Tax Table'!B4:I4,0)

 

[aja2150]

Sorry I didn't really explain the issue real well. I have two worksheets that are involved in the formula. One worksheet contains employee data such as marital status, and number of dependents. The other worksheet has the tax table. On the tax worksheet, the column B5:B13 has the different levels of income,(0,1000,1500...)The row C4:I4 contains the number of dependents,(0,1,2,....) the table then contains different percentages. For example, someone with 0 dependents and makes $750
would pay 7%.

What I am trying to do on the first worksheet is use a formula that looks up the percentage amount based on number of dependents and gross earnings.

Hope this helps clear up what I am trying to do.

 

[MrKowz]

Try using:

=INDEX('Federal Tax Table'!C5:I13,MATCH(K2,'Federal Tax Table'!B5:B13),MATCH(E2,'Federal Tax Table'!C4:I4,0))

 

[T. Valko]

Sorry I didn't really explain the issue real well. I have two worksheets that are involved in the formula. One worksheet contains employee data such as marital status, and number of dependents. The other worksheet has the tax table. On the tax worksheet, the column B5:B13 has the different levels of income,(0,1000,1500...)The row C4:I4 contains the number of dependents,(0,1,2,....) the table then contains different percentages. For example, someone with 0 dependents and makes $750
would pay 7%.

What I am trying to do on the first worksheet is use a formula that looks up the percentage amount based on number of dependents and gross earnings.

Hope this helps clear up what I am trying to do.

Let's assume this is your table:

Book1
	B	C	D	E	F	G	H	I
4		0	1	2	3	4	5	6
5	0	31	92	41	52	72	41	24
6	1000	58	78	20	67	17	60	55
7	1500	12	28	95	26	46	26	85
8	2500	56	49	23	57	35	12	10
9	5000	10	38	75	82	54	94	32
10	10000	16	18	64	82	32	95	75
11	15000	37	43	97	22	47	60	69
12	25000	42	94	38	20	97	38	38
13	50000	34	95	83	76	77	94	73
Federal Tax Table

Lookup values:

• K2 = some income amount like 22129
• E2 = number of dependents like 3

Then:

=VLOOKUP(K2,'Federal Tax Table'!B5:I13,MATCH(E2,'Federal Tax Table'!B4:I4,0))

Based on the sample table the result is 22.