How to Use More Than 7 IF Functions?

jerryj

New Member
Joined
Aug 29, 2002
Messages
4
Does anyone know a easy way to get around the 7 IF Function Limit. All help will be greatly appreciated.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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-
 
Upvote 0
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.
 
Upvote 0
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",)))))))))))
 
Upvote 0
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
ABCDE
1TargetNameValueReturn
20Ian042Ian1
340Ian1169Ian13
450Ian223Ian0
560Ian3157Ian12
670Ian478Ian4
780Ian5103Ian7
890Ian672Ian4
9100Ian7116Ian8
10110Ian8181Ian15
11120Ian9231Ian20
12130Ian1094Ian6
13140Ian11136Ian10
14150Ian12150Ian12
15160Ian13107Ian7
16170Ian14157Ian12
17180Ian15210Ian18
18190Ian16218Ian18
19200Ian1795Ian6
20210Ian18
21220Ian19
22230Ian20
23240Ian21
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,218,826
Messages
6,144,693
Members
450,565
Latest member
ChasVenie12

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top