Maximum number of IF statements in a Calculated Column?
If you said seven, or eight, you spoke too fast…
I just answered a question on the Stump the Panel forum, and thought others could be interested.
The context
I recently published a new version of my tool for color coding calendars and lists. This tool generates a formula that associates each item of a choice list with a color:
=IF([Status]=”",”Black”,IF([Status]=”Choice1″,”Red”,IF([Status]=”Choice2″,”Gold”,IF([Status]=”Choice3″,”Green”,IF([Status]=”Choice4″,”DarkBlue”,IF([Status]=”Choice5″,”DarkCyan”,IF([Status]=”Choice6″,”DarkRed”,IF([Status]=”Choice7″,”Gray”,”"))))))))
For example, if the [Status] column contains Choice5, the output of the formula will be DarkCyan.
The issue
In practice, users often have more than 7 items in their choice list. For 8 choices, the formula becomes:
=IF([Status]=”",”Black”,IF([Status]=”Choice1″,”Red”,IF([Status]=”Choice2″,”Gold”,IF([Status]=”Choice3″,”Green”,IF([Status]=”Choice4″,”DarkBlue”,IF([Status]=”Choice5″,”DarkCyan”,IF([Status]=”Choice6″,”DarkRed”,IF([Status]=”Choice7″,”Gray”,IF([Status]=”Choice8″,”Yellow”,”")))))))))
But if you try it, here is the message SharePoint throws back at you:
The formula contains a syntax error or is not supported.
So here is the sad truth: you cannot have more than seven nested IF statements in a calculated column.
When users discover this – by trial and error, via a forum, or from their experience with Excel - the next step for them is usually to try and break their formula across multiple columns, each containing a maximum of seven nested IFs. However, in most cases there is a simpler way.
The workaround
We have seen that a calculated column doesn’t allow more than seven nested IFs. But what if they are not nested? Let’s give it a try with my formula generator:
- First pass: choice1 to choice7
=IF([Status]=”",”Black”,IF([Status]=”Choice1″,”Red”,IF([Status]=”Choice2″,”Gold”,IF([Status]=”Choice3″,”Green”,IF([Status]=”Choice4″,”DarkBlue”,IF([Status]=”Choice5″,”DarkCyan”,IF([Status]=”Choice6″,”DarkRed”,IF([Status]=”Choice7″,”Gray”,”"))))))))
- Second pass: choice 8 to choice 14
=IF([Status]=”",”Black”,IF([Status]=”Choice8″,”MediumSlateBlue”,IF([Status]=”Choice9″,”SpringGreen”,IF([Status]=”Choice10″,”MidnightBlue”,IF([Status]=”Choice11″,”Sienna”,IF([Status]=”Choice12″,”SlateGray”,IF([Status]=”Choice13″,”OliveDrab”,IF([Status]=”Choice14″,”Gray”,”"))))))))
Now, I just have to concatenate these two using the & symbol:
=IF([Status]=”",”Black”,IF([Status]=”Choice1″,”Red”,IF([Status]=”Choice2″,”Gold”,IF([Status]=”Choice3″,”Green”,IF([Status]=”Choice4″,”DarkBlue”,IF([Status]=”Choice5″,”DarkCyan”,IF([Status]=”Choice6″,”DarkRed”,IF([Status]=”Choice7″,”Gray”,”"))))))))&IF([Status]=”",”Black”,IF([Status]=”Choice8″,”MediumSlateBlue”,IF([Status]=”Choice9″,”SpringGreen”,IF([Status]=”Choice10″,”MidnightBlue”,IF([Status]=”Choice11″,”Sienna”,IF([Status]=”Choice12″,”SlateGray”,IF([Status]=”Choice13″,”OliveDrab”,IF([Status]=”Choice14″,”Gray”,”"))))))))
There is still something wrong as the “” (no selection) option appears twice. So I need to remove the redundant one (in orange above, don’t forget the last parenthesis) to get my final result:
=IF([Status]=”",”Black”,IF([Status]=”Choice1″,”Red”,IF([Status]=”Choice2″,”Gold”,IF([Status]=”Choice3″,”Green”,IF([Status]=”Choice4″,”DarkBlue”,IF([Status]=”Choice5″,”DarkCyan”,IF([Status]=”Choice6″,”DarkRed”,IF([Status]=”Choice7″,”Gray”,”"))))))))&IF([Status]=”Choice8″,”MediumSlateBlue”,IF([Status]=”Choice9″,”SpringGreen”,IF([Status]=”Choice10″,”MidnightBlue”,IF([Status]=”Choice11″,”Sienna”,IF([Status]=”Choice12″,”SlateGray”,IF([Status]=”Choice13″,”OliveDrab”,IF([Status]=”Choice14″,”Gray”,”")))))))
And voila, a formula that fits in one calculated column, with 15 IF functions!
“That’s cool, Christophe! So… when can we see this in your formula generator?”
Ok, I’ll update it… but seriously, is it reasonable to use more than 7 colors for color coding?
Other options
For the record, IF is not the only SharePoint function allowing to build conditional formulas. See for example in this post how I used the CHOOSE function. And yes, there will be another formula generator built on CHOOSE!
That seemed too simple. I never would have thought of the concatenate. Great job!
Brilliant.
I agree – BRILLIANT!!! More colors are useful when you are using them to differentiate events by department or group instead of a degree of status. We have a combined calendar for several groups to list their events and people love to differentiate between the group events by color. “Oh a limegreen event! That’s me!” I guess each group knows their own color, so they are really only looking for that one color amongst the sea of many others. Make sense?
Well, I’m glad I left the question mark along with my comment
Makes perfect sense Erica.
NOTE: There is also a character limit.
http://sharepointkb.wordpress.com/2009/02/10/calculated-column-limits-1024-characters/
Basically its also limited to 1024 characters. Hope this helps someone out there,
Richard Harbridge
Right. I didn’t want to mention it now… because it will be in Larry’s article in a couple days.
I have actually identified 3 limits:
- formula length: 1024 characters
- calculation length: 2048 characters
- filter length: 256 characters
And now I recall mentioning this in my sparkline tutorial – one of my heaviest formulas ever…
Hi Christophe
Perhaps 15 is way too many for colours, but at least 12 is required when using months of the year.
I ran across this problem last year, when I had to stop a 7. Same in Excel which I got a work around for as well.
Thanks for this formula.
Iain
This made perfect sense to me and allowed me to input it into MOSS2007 calendar list fine…BUT when I add the 8th item choice it only displays the second set of colors.
Im using your formula and using an “individual” field instead of “status” field. Im also using names for choices and standard colors to display. If I cut the code in half before the &IF statement, I see 8 colors. When I add the &IF and the 9th choice and color, Im only able to see the 9th choice color. If I add out to 14 choices, I only see colors for 9-14. Do you have any ideas as to why? Ive even copied and pasted straight from your website into MOSS and modified the column names but I cant seem to find the problem.
=IF([Individual]=””,”Black”,IF([Individual]=”Chris″,”Red”,IF([Individual]=”Bob″,”Gold”,IF([Individual]=”Choice3″,”Dan”,IF([Individual]=”Richard″,”DarkBlue”,IF([Individual]=”Billy″,”DarkCyan”,IF([Individual]=”Mark″,”DarkRed”,IF([Individual]=”Jack″,”Gray”,””))))))))&IF([Individual]=”John″,”MediumSlateBlue”,IF([Individual]=”Stewart″,”SpringGreen”,IF([Individual]=”Jimmy″,”MidnightBlue”,IF([Individual]=”Kyle″,”Sienna”,IF([Individual]=”Fred″,”SlateGray”,IF([Individual]=”Marsha″,”OliveDrab”,IF([Individual]=”Susan″,”Gray”,””)))))))
Im stumped.
Nevermind, I found it. I typed it right when I replied, but I forgot this , and thats what caused the problem.
Gray”,””)))))))
The nested IF conditionals here are EUREKA – and not related to the color coding intiative. I have been in a place before when I needed to organize a list for display or reporting (think “supercategory” gathering of items by “category” – and ran into the 7 conditional limit. Thanks for the illumination of a way to bust out beyond that!!!!
This is fantastic! Exactly what I was looking for. Thank you
What a timesaver…. Thanks!
So…
=IF([Event Type]=”AAAHC”,”#990000″,IF([Event Type]=”ASEV”,”#000066″,IF([Event Type]=”ATSEP”,”#996600″,IF([Event Type]=”COMSEC”,”#339900″,IF([Event Type]=”ESOHCAMP”,”#000099″,IF([Event Type]=”HSI”,”#CC6600″,IF([Event Type]=”LCAT”,”#996600″)))))))&IF([Event Type]=”ORI”,”#FFFFFF”,IF([Event Type]=”PME”,”#660099″,IF([Event Type]=”SEPWO”,”#000000″,IF([Event Type]=”UCI”,”#FFFFFF”))))
results in …
COMSEC = #339900FALSE
AAAHC = #990000FALSE
LCAT = #996600FALSE
ASEV = #000066
…etc.
…and the ones below the “&”
UCI = FALSE#FFFFFF
ORI = FALSE#FFFFFF
…etc.
So I obvisouly want to get ride of the “FALSE”, what am I missing?
In the first part of your expression, before the ampersand, you need to return nothing (“”) if no condition is met.
Something like this:
Same in the second part, after the ampersand.
Rock on! That was it, thanks bro!!
I’m trying to use this technique to set a Status text column automatically when certain milestone date columns have a value. My formula is below. It works perfectly if none of the conditions before the & are met, but when one of the conditions before the & is true, it displays the text for that condition and the text for the first IF statement following the &.
=IF([Production Release Date]>0,”In Production”,
IF([Code Delivered]>0,”Client Testing”,
IF([BA Testing Completed]>0,”Ready for Delivery”,
IF([BA Testing Started]>0,”BA Testing”,
IF([Developer Testing Started]>0,”Developer Testing”,
IF([Development (Coding) Started]>0,”In Development”,
IF([Functional Spec Approved]>0,”Approved for Coding”,”")))))))
&IF([Functional Spec Complete]>0,”Functional Spec Review”,
IF([Functional Spec Started]>0,”Functional Spec Started”,
IF([BRD Received]>0,”Requirements Review”,
IF([Start Date]>0,”Awaiting Approved Requirements”))))
For example, If [Start Date] through [Functional Spec Started] dates have values, “Funtional Spec Review” is displayed (which is correct). But if [Start Date] through [Developer Testing Started] have values, “Developer TestingFunctional Spec Review” is displayed.
Is there a way around this? Thanks for your help!
Nate, same reply as for johnnybru!
My code is below….I’m still getting “6FALSE” and “5FALSE” when page is refreshed. What am I doing wrong?
=IF([Rank / Title]=”Gen”,1,IF([Rank / Title]=”Lt Gen”,2,IF([Rank / Title]=”Maj Gen”,3,IF([Rank / Title]=”Brig Gen”,4,IF([Rank / Title]=”GS-15″,4.1,IF([Rank / Title]=”Col”,5,IF([Rank / Title]=”GS-14″,5.1,IF([Rank / Title]=”Lt Col”,6,”"))))))))&IF([Rank / Title]=”CMSgt”,6.1,IF([Rank / Title]=”GS-13″,6.2,IF([Rank / Title]=”Maj”,7,IF([Rank / Title]=”SMSgt”,7.1,IF([Rank / Title]=”GS-12″,7.2,IF([Rank / Title]=”MSgt”,8,IF([Rank / Title]=”Capt”=8.1,IF([Rank / Title]=”GS-11″,8.2,”"))))))))
Great Solution! Would have never come up with it on my own and it worked perfectly!
That is indeed Brilliant!
However I have a situation where I have more than 15 items I need to loop. Not for colors , but I have a list of names and titles. Using ‘&’ works once…so is there a work around for that as well?? I know…greedy me!!
Rinse and repeat! The same tip will apply to 22 statements, or 29, etc.
If you reach the maximum number of characters allowed in one formula, then you’ll need to use more than one calculated column.
Hi, great workaround. Following on from the last comment, is it true you can only use ‘&’ once with this workaround? I have 19 conditions but only want to display results in 1 calculated column. Thanks
Michael, you can use ‘&’ several times in the same calculated column.
This is EXCELLENT. Am a new SP2007 Site Collection Admin and found your tutorial on adding 7 colors to calendars. Can you tell me where to find the one which allows more colors?
Actually looking for the “updated formula generator”. THANKS!
Hi Guys,
I have to solve my nested issue. These are 3 If’s that I need to concatenate. I’m tring to do it but without any success. Please is there sonmeone able to help me please.
Gianni
>>>> Linea1 =IF(AND(Importo>50000,Importo35%),”Orange”,IF(AND(Importo>100000,Importo35%),”Red”,IF(AND(Importo>1000000,StandardTerms=”Yes”,RiskFactors=”No”,GM>35%),”Blu”,”NO”)))
>>>> Linea2 =IF(AND(Importo>25000,Importo25%,GM100000,Importo25%,GM500000,StandardTerms=”Yes”,RiskFactors=”No”,GM>25%,GM>>> Linea3 =IF(AND(Importo>25000,Importo<100000,StandardTerms="Yes",RiskFactors="No",GM500000,StandardTerms=”Yes”,RiskFactors=”No”,GM<25%),"Blu","NO"))
This just save my life, thank you so much!
The work around is great. I had to calculate the month numbers from the month name so I used your solution but changed it up some:
=IF(Month=”JANUARY”,1,IF(Month=”February”,2,IF(Month=”March”,3,IF(Month=”APRIL”,4,IF(Month=”MAY”,5,0)))))+IF(Month=”JUNE”,6,IF(Month=”JULY”,7,IF(Month=”AUGUST”,8,IF(Month=”SEPTEMBER”,9,IF(Month=”OCTOBER”,10,0)))))+IF(Month=”NOVEMBER”,11,IF(Month=”DECEMBER”,12,0)
Worked perfectly.
I’ve got this to work fine on a Rank system and it assigned the values perfectly (21 different rank values with 3 nested ‘&’ statements). However, when I try to sort on the value of this column it doesn’t work as expected, they are still all over the place rather than sequentially… any ideas?
No idea… Sorting should work on the output.
Hi
I successfully implemented your tool for 7 choices. However, I cannot seem to get this to work for our 10 choices. I even re-created your scenario exactly (created a Status Column with 14 Choices) and then copied and pasted your code as is, to check whether my changes for our scenario caused the problem. In both cases I get the error below. What am I doing wrong?
The formula contains a syntax error or is not supported. at Microsoft.SharePoint.Library.SPRequestInternalClass.UpdateField(String bstrUrl, String bstrListName, String bstrXML)
at Microsoft.SharePoint.Library.SPRequest.UpdateField(String bstrUrl, String bstrListName, String bstrXML)
Do not directly copy/paste from this article, as the quotes are styled. You need to use regular quotes in the formula.
Thanks so much Christophe! It works perfectly now (even for a non-IT person)
What am i doing wrong – below is the code I am using.
=IF([LikeLihood x Impact]=”",”",IF([LikeLihood x Impact]=”1×1″,”Low”,IF([LikeLihood x Impact]=”1×2″,”Low”,IF([LikeLihood x Impact]=”1×3″,”Low”,IF([LikeLihood x Impact]=”1×4″,”Low”,IF([LikeLihood x Impact]=”1×5″,”Medium”,IF([LikeLihood x Impact]=”2×1″,”Low”,IF([LikeLihood x Impact]=”2×2″,”Low”,IF([LikeLihood x Impact]=”2×3″,”Low”,IF([LikeLihood x Impact]=”2×4″,”Medium”,IF([LikeLihood x Impact]=”2×5″,”Medium”,IF([LikeLihood x Impact]=”3×1″,”Low”,IF([LikeLihood x Impact]=”3×2″,”Medium”,IF([LikeLihood x Impact]=”3×3″,”Medium”,IF([LikeLihood x Impact]=”3×4″,”Medium”,IF([LikeLihood x Impact]=”3×5″,”High”))))))))))))))))&IF([LikeLihood x Impact]=”",”",IF([LikeLihood x Impact]=”4×1″,”Low”,IF([LikeLihood x Impact]=”4×2″,”Medium”,IF([LikeLihood x Impact]=”4×3″,”Medium”,IF([LikeLihood x Impact]=”4×4″,”High”,IF([LikeLihood x Impact]=”4×5″,”High”,IF([LikeLihood x Impact]=”5×1″,”Low”,IF([LikeLihood x Impact]=”5×2″,”Medium”,IF([LikeLihood x Impact]=”5×3″,”High”,IF([LikeLihood x Impact]=”5×4″,”High”,IF([LikeLihood x Impact]=”5×5″,”High”,)))))))))))
Each IF function needs 3 arguments. I think you’re missing the third argument at the very end (empty string: “”).
I have an issue that has me stumped – I’m trying to build an IF statement to extract a month value for one of my lists.
Here’s my formula: =IF([Start Date]=”",”Missing A Date”,IF([Start Date]<="1/31/2012","01-JAN",IF([Start Date]<="2/28/2012","02-FEB",)))
Problem is even with records that have a start date with FEB in them, still returns 01-JAN. If date record is blank – returns "Missing A Date". I found another way around to get desired result but I'm curious why it keeps returning 01-JAN. Need a push in proper direction. Thanks
You are comparing a date to a string. In SharePoint, dates are stored as number of days.
Christophe,
Thanks, this has been very helpful.
I had a requirement to colour code a Project Risk Register. The first 2 values were selected from lists, so the technique described here and in your other posts on lists worked fine. The 3rd value was a Risk Score calculated from the first 2 values, therefore the above formula wouldn’t work. Anyhow, after a little research & head scratching I came up with the following, which when integrated with the rest of your published technique allowed me to colour code a calculated value where the colour is determined by the range of the calculated value between 1 & 30. If its less than 5, then Yellow. If its between 5 & 14, then Orange. If its between 15 & 23, then Red. It its greater than 24 then Indigo.
=”<span style='width:100%;display:inline-block;text-align:center;border:1px solid"
&IF(RSV4,RSV14,RSV24,”Indigo”))))
&”;background-color:”
&IF(RSV4,RSV14,RSV24,”Indigo”))))
&”;’> “&RSV&”"
Hope this is helpful for someone else
The code should be:
=”<span style='width:100%;display:inline-block;text-align:center;border:1px solid"
&IF(RSV4,RSV14,RSV24,”Indigo”))))
&”;background-color:”
&IF(RSV4,RSV14,RSV24,”Indigo”))))
&”;’> “&RSV&”"
Thank you Christophe!
I was able to tweak your formula to work for my project – I have one column with a drop-down list of the states, and needed to create a second column that calculated the EPA Region (1-10) based on the state selected:
=IF(State=”CT”,1,IF(State=”ME”,1,IF(State=”MA”,1,IF(State=”NH”,1,IF(State=”RI”,1,IF(State=”VT”,1,IF(State=”NJ”,2,0)))))))+
IF(State=”NY”,2,IF(State=”DE”,3,IF(State=”DC”,3,IF(State=”MD”,3,IF(State=”PA”,3,IF(State=”VA”,3,IF(State=”WV”,3,0))))))+
IF(State=”AL”,4,IF(State=”FL”,4,IF(State=”GA”,4,IF(State=”KY”,4,IF(State=”MS”,4,IF(State=”NC”,4,IF(State=”SC”,4,0)))))))+
IF(State=”TN”,4,IF(State=”IL”,5,IF(State=”IN”,5,IF(State=”MI”,5,IF(State=”MN”,5,IF(State=”OH”,5,IF(State=”WI”,5,0)))))))+
IF(State=”AR”,6,IF(State=”LA”,6,IF(State=”NM”,6,IF(State=”OK”,6,IF(State=”TX”,6,IF(State=”IA”,7,IF(State=”KS”,7,0)))))))+
IF(State=”MO”,7,IF(State=”NE”,7,IF(State=”CO”,8,IF(State=”MT”,8,IF(State=”ND”,8,IF(State=”SD”,8,IF(State=”UT”,8,IF(State=”WY”,8,0))))))))+
IF(State=”AZ”,9,IF(State=”CA”,9,IF(State=”HI”,9,IF(State=”NV”,9,IF(State=”AK”,10,IF(State=”ID”,10,IF(State=”OR”,10,IF(State=”WA”,10,0))))))))
As you used in your example, ” ” with an ampersand at the end of each nested IF statement string also worked.
Region map: http://www.epa.gov/epahome/images/us-regions315.gif
Not bad Chris. keep up all the good work!
NB: a perhaps simplier option (much easier to maintain) would be results of the following:
=CONCATENATE(
IF( [Field]=”Option A”, “Color A”, “”)
,IF( [Field]=”Option B”, “Color B”, “”)
,IF( [Field]=”Option C”, “Color C”, “”)
….
,IF( [Field]=”Option n”, “Color “, “”)
)
Much easier to edit afterwards.
Definitely easier to read. The downside is that it won’t work if the default value is not blank.
Okay Chris i need your help I saw in a previous post for the same question but the answer was not clear to me. I am following your instructions and but i get a “AgenciesFALSE” back. how do i fix this?
here is my formula:
=IF(LEFT([FP Number],2)=”WF”,”Agencies”,IF(LEFT([FP Number],2)=”WR”,”Funds”,IF(LEFT([FP Number],2)=”WU”,”Work Orders”,IF(LEFT([FP Number],2)=”WW”,”Reimbursable”,IF(LEFT([FP Number],2)=”WT”,”Agreement”,IF(LEFT([FP Number],2)=”WY”,”Services”,IF(LEFT([FP Number],2)=”MR”,”Technical”)))))))&IF(LEFT([FP Number],2)=”WK”,”Work”,IF(LEFT([FAP Number],2)=”WO”,”Others”))
Lisa –
try adding “” at the end of each nested section:
…..=”MR”,”Technical”,”")))))))
…..=”WO”,”Others”,”"))
That worked!!!! Thank you soooo much!!!
How to make color diplay on Week View?
Here’s my issue. I have successfully embedded the nested IF’s. My issue resides in the logic of my formulas. If SharePoint find a Denominator value of ZERO or EMPTY it replaces it with a 0 (the reason of this I have to do a division, hence the search and replace). when I add the & to consider a particular set of values, the Formula ends up concatenating the original calculation (Numerator / Denominator) and the 0 I’m asking to put as a replacement. I need that in the end if uses on of the 2 possible values…Need Help. Here’s my latest attempt:
=IF([Library Key]=”0454″,(1-(Numerator/Denominator)),IF([Library Key]=”0443″,(1-(Numerator/Denominator)),IF([Manual Calculation]“”,[Manual Calculation],IF(Denominator=0,0,IF(Denominator=”",”",IF(Data_Type=”System Generated”,(Numerator/Denominator),[Manual Calculation]))))))&IF([Library Key]=”0142″,Numerator,IF([Library Key]=”0146″,Numerator,IF([Library Key]=”0147″,Numerator,IF([Library Key]=”0148″,Numerator,IF([Library Key]=”1793″,Numerator,IF([Library Key]=”3239″,Numerator,IF([Library Key]=”0053″,0,”")))))))
To get a clean formula, you should test the value of your denominator before doing any calculation.