How to Use More Than 7 IF Functions?

[jerryj]

Does anyone know a easy way to get around the 7 IF Function Limit. All help will be greatly appreciated.

 

[johnpdavey]

try using vlookup.
this allows you to have as many options as you require

 

[gwkenny]

Within a cell?

Breakup your formula to span more than one cell. At the top of your IF statement tree you have =if(A,y,z). Put Y-tree in one cell. Put Z-tree in another cell.

g-

 

[Aladin Akyurek]

On 2002-08-30 00:41, jerryj wrote:
Does anyone know a easy way to get around the 7 IF Function Limit. All help will be greatly appreciated.

See my contrib for an example of how to avoid chaining and/or nesting too many IFs in:

http://www.mrexcel.com/board/viewtopic.php?topic=20140&forum=2

 

[Ian Mac]

On 2002-08-30 00:41, jerryj wrote:
Does anyone know a easy way to get around the 7 IF Function Limit. All help will be greatly appreciated.

Welcome to the board Jerry,

Can you post a copy of your formula using IF's.
I think, as mentioned, you may be able to use VLOOKUP or LOOKUP.
If you post the formula we'll have a look.

Also, as mentioned, you can run across a number of cells. This would only be a workaround, look at, "limits in Microsoft Excel" in the help files.

 

[jerryj]

Here it is!!
=IF(OR(V1<=V40,V1<V41),"The Eagle Has Landed",IF(AND(V1>V40,V1<=V41),"Philly Pride II",IF(AND(V1>V41,V1<=V42),"Egoholics",IF(AND(V1>V42,V1<=V43),"Hail Marys",IF(AND(V1>V43,V1<=V44),"Rice A Roni",IF(AND(V1>V44,V1<=V45),"Dallas Devestators",IF(AND(V1>V45,V1<=V46),"Packers2003",IF(AND(V1>V46,V1<=V47),"Clutch Ballers",IF(AND(V1>V47,V1<=V48),"Hamilton Hurricanes",IF(AND(V1>V48,V1<=V49),"Womping Wombats",IF(AND(V1>V49,V1<=V50),"Under Dogs",)))))))))))

 

[Ian Mac]

OK,

I didn't follow Aladin link so my apologies if it's the same thing.

As i can tell you have various values in column V as as a target between numbers.

VLOOKUP should do the trick:

Book1
	A	B	C	D	E
1	Target	Name		Value	Return
2	0	Ian0		42	Ian1
3	40	Ian1		169	Ian13
4	50	Ian2		23	Ian0
5	60	Ian3		157	Ian12
6	70	Ian4		78	Ian4
7	80	Ian5		103	Ian7
8	90	Ian6		72	Ian4
9	100	Ian7		116	Ian8
10	110	Ian8		181	Ian15
11	120	Ian9		231	Ian20
12	130	Ian10		94	Ian6
13	140	Ian11		136	Ian10
14	150	Ian12		150	Ian12
15	160	Ian13		107	Ian7
16	170	Ian14		157	Ian12
17	180	Ian15		210	Ian18
18	190	Ian16		218	Ian18
19	200	Ian17		95	Ian6
20	210	Ian18
21	220	Ian19
22	230	Ian20
23	240	Ian21
Sheet1

using the 1 at the end of the formula will give an approximate match (round down) to the one your looking for.

Is this what you need?

 

[thereuare]

Just adding a "work-around" that i've used that i believe is simpler than the above.

Split your IF statements between 2 cells, in your example you have 9 IF statements. In the first column use the first 7, but have the ultimate FALSE print an empty cell (ie- " ").

Then have a second column check the criteria on the other 2 IF statements, with the ultimate FALSE printing an empty cell.

Then in a 3rd column do an IF statement that displays the value of the cell that isn't empty (ie- =If(a1=" ", b1, a1) ).
This message was edited by thereuare on 2002-08-31 06:42

 

[Aladin Akyurek]

On 2002-08-30 09:14, jerryj wrote:
Here it is!!
=IF(OR(V1<=V40,V1<V41),"The Eagle Has Landed",IF(AND(V1>V40,V1<=V41),"Philly Pride II",IF(AND(V1>V41,V1<=V42),"Egoholics",IF(AND(V1>V42,V1<=V43),"Hail Marys",IF(AND(V1>V43,V1<=V44),"Rice A Roni",IF(AND(V1>V44,V1<=V45),"Dallas Devestators",IF(AND(V1>V45,V1<=V46),"Packers2003",IF(AND(V1>V46,V1<=V47),"Clutch Ballers",IF(AND(V1>V47,V1<=V48),"Hamilton Hurricanes",IF(AND(V1>V48,V1<=V49),"Womping Wombats",IF(AND(V1>V49,V1<=V50),"Under Dogs",)))))))))))

What are the values that you have in V40:V50?

 

[Ian Mac]

On 2002-08-31 06:41, thereuare wrote:
Just adding a "work-around" that i've used that i believe is simpler than the above.

Split your IF statements between 2 cells, in your example you have 9 IF statements. In the first column use the first 7, but have the ultimate FALSE print an empty cell (ie- " ").

Then have a second column check the criteria on the other 2 IF statements, with the ultimate FALSE printing an empty cell.

Then in a 3rd column do an IF statement that displays the value of the cell that isn't empty (ie- =If(a1=" ", b1, a1) ).
This message was edited by thereuare on 2002-08-31 06:42

A work-around? Would be a lot more costly on system resource when it calculates.

My reply, which may not answer the question (until we have further information),
Let the user keep a track of all the different values between.
Also, the way the problem is described, it sound like such a table already exsists, why would run a formula over 2, 3 or 4 cells when VLOOKUP is design for just such a table?

I'll wait for a reply to Aladin's question until I fully commit to an answer.

Regards