Nested IF functions

dph95 · Jul 6, 2008

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.

LTunnicliffe · Jul 7, 2008

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 · Jul 7, 2008

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:

PHP:

=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 · Jul 7, 2008

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 · Jul 8, 2008

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 · Jul 8, 2008

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

AY

AZ

BA

BB

BC

BD

BE

BF

BG

BH

1

Overall

Cat

BARN

BONE

2

Time

Pos

Prize

Team

Rank

ClubA

ClubB

3

25:10

76