Help with a huge nested if formula!

[kc2equ]

ok... two cells the first one has a dollar amount in it and the seond one has a mulitplier baised on the following information. I have been able to do this before with nested IF statements, but I can't seem to get this to work. Please help!

Thanks, Justin

what I tried:

=IF(N52>=185,7.75,IF(N52>=180,7.5,IF(N52>=175,7.25,IF(N52>=170,7,IF(N52>=165,6.75,IF(N52>=160,6.5,if(N52>=155,6.25,IF(N52>=150,6,IF(N52>=145,5.75,IF(N52>=140,5.5,IF(N52>=135,5.25,IF(N52>=130,5,IF(N52>=125,4.75,IF(N52>=120,4.5,IF(N52>=115,4.25,IF(N52>=110,4,IF(N52>=105,3.75,IF(N52>=100,3.5,IF(N52>=95,3.25,IF(N52>=90,3,IF(N52>=85,2.75,IF(N52>=80,2.5,IF(N52>=75,2.25,IF(N52>=70,2,IF(N52>=65,1.75,IF(N52>=60,1.5,IF(N52>=55,1.25,IF(N52>=50,1,IF(N52>=45,.75,IF(N52>=40,.5,IF(N52>=35,.25,0)))))))))))))))))))))))))))))))

The table:
185 AND UP 7.75
180 - 184.00 = 7.5
175 - 179.99 = 7.25
170 - 174.99 = 7
165 - 169.99 = 6.75
160 - 164.99 = 6.5
155 - 159.99 = 6.25
150 - 154.00 = 6
145 - 149.99 = 5.75
140 - 144.99 = 5.5
135 - 139.99 = 5.25
130 - 134.99 = 5
125 - 129.99 = 4.75
120 - 124.99 = 4.5
115 - 199.99 = 4.25
110 - 114.99 = 4
105 - 109.99 = 3.75
100 - 104.99 = 3.5
95 - 99.99 = 3.25
90 - 94.99 = 3
85 - 89.99 = 2.75
80 - 84.99 = 2.5
75 - 89.99 = 2.25
70 - 74.99 = 2
65 - 69.99 = 1.75
60 - 64.99 = 1.5
55 - 59.99 = 1.25
50 - 54.99 = 1
45 - 49.99 = .75
40 - 44.99 = .5
35 - 39.99 = .25
0 - 34.99 = 0

 

[Smitty]

You can only nest 7 IF statements at once.

You might be better off looking at setting up your table and using LOOKUP or VLOOKUP.

Your example is close to being set up that way already, it just needs to be parsed.

The helpfile documents both LOOKUPS pretty well.

HTH,

Smitty

 

[Brian from Maui]

Use Vlookup defaulting to True. You already have a table.

 

[kc2equ]

I can't seem to figure the vlookup function out. I put all of the info into a cells. How do i confirgure function to give me a value?

Thanks,
Justin



185 7.75
180 7.5
175 7.25
170 7
165 6.75
160 6.5
155 6.25
150 6
145 5.75
140 5.5
135 5.25
130 5
125 4.75
120 4.5
115 4.25
110 4
105 3.75
100 3.5
95 3.25
90 3
85 2.75
80 2.5
75 2.25
70 2
65 1.75
60 1.5
55 1.25
50 1
45 .75
40 .5
35 .25
0 - 34.99 0

 

[PA HS Teacher]

You mus have your list sorted in Ascending order


In D1 below:

Book1
	A	B	C	D	E
1	0	0		0.25	39.99
2	35	0.25		0.5	40
3	40	0.5
4	45	0.75
5	50	1
6	55	1.25
7	60	1.5
8	65	1.75
9	70	2
10	75	2.25
11	80	2.5
12	85	2.75
13	90	3
14	95	3.25
15	100	3.5
16	105	3.75
17	110	4
18	115	4.25
19	120	4.5
20	125	4.75
21	130	5
22	135	5.25
23	140	5.5
24	145	5.75
25	150	6
26	155	6.25
27	160	6.5
28	165	6.75
29	170	7
30	175	7.25
Sheet1

 

[kc2equ]

ok, i have set them up in Ascending order. Now I have 2 cells 1 has the total in it.. ( ie 100 ) and the other needs the corosponding answer in it ( ie 100 would equal 3.5 ) how do I call the table from the cell?

Thanks,
Justin

 

[PA HS Teacher]

I forgot to include the vlookup formula.

In D1 of my example above

=VLOOKUP(E1,A$1:B$32,2,1)

E1 contains the value to look up, in your case 100, in my example above 39.99.

Notice the 1 for the third argument of the Vlookup. Your table must be in ascending order. If an exact match is not found, it will use the largest value that is less than or equal to the value in E1. (if the 3rd argument were false or 0, an exact match would be required, else you would get the #N/A Error)

The 2 tells the Vlookup to pull the corresponding value from the 2nd column.

 

[kc2equ]

works great!! you guys rock!

Thanks for everything!

Justin