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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

gwkenny

Well-known Member
Joined
Aug 13, 2002
Messages
565
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-
 

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174

ADVERTISEMENT

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

New Member
Joined
Aug 29, 2002
Messages
4
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

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174

ADVERTISEMENT

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?
 

thereuare

Board Regular
Joined
May 28, 2002
Messages
232
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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
 

Forum statistics

Threads
1,144,433
Messages
5,724,317
Members
422,543
Latest member
TimB_13

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
Top