# Nested IF functions

#### dph95

##### New Member
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.

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### LTunnicliffe

##### Well-known Member
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
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
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

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
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
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
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
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:

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,092
Messages
5,835,344
Members
430,351
Latest member
ddalton

### 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.

### Which adblocker are you using?

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

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