Nested IF functions

dph95

New Member
Joined
Jul 6, 2008
Messages
8
I am using a formula for a sports result sheet. It displays whether a competitor is one of the teams in the top 4, and also their place within that team (which are made up of three people) (e.g the output could be "1st-1" "1st-2" "4th-3"etc.) The formula will be used for each of the 100 competitors.

Last year I had nine teams, which exceeded the maximum 7 nested IF functions allowed. I managed to get round this by using CONCATENATE. However this year I have 16 teams and when I try to add to my existing formula I reach the maximum character limit of 1024.

Grateful for any ideas. The foumala is below. It is not
pretty, but it worked for nine teams (TrankA, TrankB etc. are defined names which give the overall position of the team)

=IF(AZ20="","",CONCATENATE(IF(AND(BF20>=1,BF20<=3,TrankA>=1,TrankA<=4),CHOOSE(TrankA,"1st","2nd","3rd","4th")&"-"&BF20,""),IF(AND(BH20>=1,BH20<=3,TrankB>=1,TrankB<=4),CHOOSE(TrankB,"1st","2nd","3rd","4th")&"-"&BH20,""),IF(AND(BJ20>=1,BJ20<=3,TrankC>=1,TrankC<=4),CHOOSE(TrankC,"1st","2nd","3rd","4th")&"-"&BJ20,""),IF(AND(BL20>=1,BL20<=3,TrankD>=1,TrankD<=4),CHOOSE(TrankD,"1st","2nd","3rd","4th")&"-"&BL20,""),IF(AND(BN20>=1,BN20<=3,TrankE>=1,TrankE<=4),CHOOSE(TrankE,"1st","2nd","3rd","4th")&"-"&BN20,""),IF(AND(BP20>=1,BP20<=3,TrankF>=1,TrankF<=4),CHOOSE(TrankF,"1st","2nd","3rd","4th")&"-"&BP20,""),IF(AND(BR20>=1,BR20<=3,TrankG>=1,TrankG<=4),CHOOSE(TrankG,"1st","2nd","3rd","4th")&"-"&BR20,""),IF(AND(BT20>=1,BT20<=3,TrankH>=1,TrankH<=4),CHOOSE(TrankH,"1st","2nd","3rd","4th")&"-"&BT20,""),IF(AND(BV20>=1,BV20<=3,TrankJ>=1,TrankJ<=4),CHOOSE(TrankJ,"1st","2nd","3rd","4th")&"-"&BV20,"")

I guess VBA may be the answer, but I know very little about that.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

LTunnicliffe

Well-known Member
Joined
May 8, 2003
Messages
767
Hello dph95 and welcome to the board,

I doubt that this is going to require VBA but it is certainly going to require more information about your data. What does your data look like? What do you want to get out of the data?

Post some sample data.

Loren
 

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274
Last year I had nine teams, which exceeded the maximum 7 nested IF functions allowed

well, there is no limit to the number of IF conditions you can have in a formula, whichever version of Excel you are using. The idea is to use VBA or use the ampersand (&) operator to nest more than 7 IF() statements.

For Example:

Code:
=IF($IE2="CHURCHGATE","B0","")&IF($IE2="NEW DELHI","B8","")&IF($IE2="GHATKOPAR","B20","")&IF($IE2="KOLKATA","B6","")&IF($IE2="AHMEDABAD","B4","")&IF($IE2="INDORE","B16","")&IF($IE2="VILE PARLE","B2","")&IF($IE2="MALAD","B23","")&IF($IE2="BARODA","B13","")&IF($IE2="HYDERABAD","B9","")&IF($IE2="PUNE","B10","")&IF($IE2="PM ROAD","B1","")&IF($IE2="ANDHERI","B21","")&IF($IE2="RAJKOT","B15","")&IF($IE2="SURAT","B14","")&IF($IE2="BANGALORE","B5","")&IF($IE2="MALLESHWARAM","B19","")&IF($IE2="BORIVALI","B3","")&IF($IE2="CHENNAI","B7","")&IF($IE2="HO DIRECT FRANCHISEE","FR","")&IF($IE2="JAIPUR","B27","")&IF($IE2="SECUNDERABAD","B26","")

Once I had written this formula long back and I took help of the ampersand operator to nest more than 7 IF() statements. I hope this would give you a more clear idea of how you want to implement the logic in your IF() statements :)
 

dph95

New Member
Joined
Jul 6, 2008
Messages
8
Thanks Stormseed. I now understand better how using the & can get round the 7 nest limit. (although I think that it is in effect just a slightly different way of doing what I am currently with CONCATENATE?)

My problem however will still be the 1024 character limit for a forumla.

Thanks also for your interest Loren. Data as requested (doing this from memory as do not have access to the worksheet on this PC)

The forumla appears in a 'Prize' column, with each competitor on a different row.

Each team has two hidden columns with space for a time and a rank (columns BE-BV). The competitors time is linked to whichever column his team is (e.g. BE), and he is then ranked within all the times for that team (BF).

The formula then checks each of the team time columns in turn (e.g. BF, BH etc.) to see if the competitor is in the top three of his team. If he is then it checks to see whether his team is within the top 4 teams (using defined name TrankA etc. which is worked out elsewhere) If so then it outputs the team ranking and the competitors place within the team eg. "1st-2" (= 1st team, 2nd counter)
"2nd-1"
"4th-3" etc.

Many thanks for any help.
 

LTunnicliffe

Well-known Member
Joined
May 8, 2003
Messages
767

ADVERTISEMENT

Is there anyway that you can use colo's html converter and take a screen capture of your data? I am still not clear as to what your data looks like.
 

dph95

New Member
Joined
Jul 6, 2008
Messages
8
Steep learning curve! Have installed the html converter and taken a snapshot of the relevant part of the data (the formula above referred to row 20, have only taken rows 1-6 because of size, but the formula is the same and is in column BC).

Many thanks for your time on this.

The way it works is in columns BE, BG etc. I manually paste in =IF($AY3="","",$AY3) in the column that corresponds to the team/club the competitor is in. When his time is then entered in AY it is copied across to the club columns (which are by then hidden) for the calculations.<SCRIPT language=JavaScript src="<a href=" popup.js" colo puremis sun www.interq.or.jp http:></SCRIPT>
Champs result sheet 2008 v2.xls
AYAZBABBBCBDBEBFBGBH
1OverallCatBARNBONE
2TimePosPrizeTeamRankClubAClubB
325:1076     25:105
420:315= 3rd-2 20:312 
520:122 2ndSnr1st-1  20:121
623:5759=   23:575 
Velo
<CENTER></CENTER></CENTER>
 
Last edited:

Chris72

Board Regular
Joined
Jun 11, 2007
Messages
115
Excel 2007 has 64 levels of functions... But...

Why would you not simply..

1. Rank all the competitor times from 1 to 100 and then
2. Rank the lowest combined times of the team and then
3. Sort each team to find each member's position within the team

instead of utilising so many IF's for the same repeating internal equation?
 

dph95

New Member
Joined
Jul 6, 2008
Messages
8
The idea is that the outcome is displayed in BC in 'realtime' i.e. as soon as the time is input in AY it displays the team and competitor position (if top 4 and 3 respectively). It is not therefore possible to sort the hidden columns.
 

Chris72

Board Regular
Joined
Jun 11, 2007
Messages
115
Thats no problem.... just record a macro to complete the sort task and assign it to a button or event.

The other thing you can do is break up the Nest into seperate individual components in different cells and then combine them with a smaller equation.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,474
Members
414,070
Latest member
DuncanLucas

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