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

## 73 thoughts on “Maximum number of IF statements in a Calculated Column?”

1. Larry says:

That seemed too simple. I never would have thought of the concatenate. Great job!

2. Nancy says:

Brilliant.

3. Erica says:

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?

• Christophe says:

Well, I’m glad I left the question mark along with my comment 😉 Makes perfect sense Erica.

• Christophe says:

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

• Christophe says:

And now I recall mentioning this in my sparkline tutorial – one of my heaviest formulas ever…

4. Iain Munro says:

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

5. Christopher Wildermuth says:

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.

6. Christopher Wildermuth says:

Nevermind, I found it. I typed it right when I replied, but I forgot this , and thats what caused the problem.

Gray”,””)))))))

7. Dan says:

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

8. silvia says:

This is fantastic! Exactly what I was looking for. Thank you

9. brian says:

What a timesaver…. Thanks!

10. johnnybru says:

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?

• Christophe says:

In the first part of your expression, before the ampersand, you need to return nothing (“”) if no condition is met.
Something like this:

```IF([Event Type]="LCAT","#996600","")))))))&...
```

Same in the second part, after the ampersand.

11. johnnybru says:

Rock on! That was it, thanks bro!!

12. Nate says:

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 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([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!

• Christophe says:

Nate, same reply as for johnnybru!

13. Matt says:

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,””))))))))

14. Ben Stegink says:

Great Solution! Would have never come up with it on my own and it worked perfectly!

• Kay says:

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

• Christophe says:

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.

15. Michael says:

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

• Christophe says:

Michael, you can use ‘&’ several times in the same calculated column.

16. Amy says:

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?

• Amy says:

Actually looking for the “updated formula generator”. THANKS!

17. Gianni says:

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"))

18. Anonymous says:

This just save my life, thank you so much!

19. Dan says:

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.

20. Des says:

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?

• Christophe says:

No idea… Sorting should work on the output.

21. Anonymous says:

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)

• Christophe says:

Do not directly copy/paste from this article, as the quotes are styled. You need to use regular quotes in the formula.

• Anonymous says:

Thanks so much Christophe! It works perfectly now (even for a non-IT person)

22. Jen says:

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”,)))))))))))

23. Christophe says:

Each IF function needs 3 arguments. I think you’re missing the third argument at the very end (empty string: “”).

• geekminer says:

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

• Christophe says:

You are comparing a date to a string. In SharePoint, dates are stored as number of days.

24. Steve says:

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

• Steve says:

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&””

25. Courtney says:

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.

26. Dave Balsillie says:

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.

• Christophe says:

Definitely easier to read. The downside is that it won’t work if the default value is not blank.

27. Lisa says:

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”))

• Courtney says:

Lisa –

try adding “” at the end of each nested section:
…..=”MR”,”Technical”,””)))))))
…..=”WO”,”Others”,””))

• Lisa says:

That worked!!!! Thank you soooo much!!!

28. H says:

How to make color diplay on Week View?

• Hotmailmild says:

Hi, can you give me any idea on how to host an infopath form on ofifce 365 with anonymous access? I know there is no support for this but any workaround or tricks available to make it done. thanks

29. Mika says:

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,””)))))))

• Christophe says:

To get a clean formula, you should test the value of your denominator before doing any calculation.

30. Iain Munro says:

Hi Christophe

I know this works, but when I used it in this scenario, I get double entries.

for example, DC Review is empty, it should jsut say With DC for Review, but I actually get With DC for ReviewDC PDF & HETrans to FC which is combination of the first item in the first nested statement along with the first item in the 32 nested statement.

I can see why it is happening, but cannot figure out how to fix it.

Any ideas would be appreciated.

=IF([DC Review]=””,”With DC for Review”,
IF([Drafting Complete]=””,”Being Drafted”,
IF([Drafting Print Copy]=””,”Being Printed for Review”,
IF([PE Review]=””,”With Calgary PE for Review”,
IF([Sent to Site PE]=””,”With FC Site PE for Review”,
IF([DC File Received]=””,”Still with FC Site PE”,
IF([Drafting Update]=””,”Drafting Update following FC Site Review”,””)))))))
&IF([DC PDF & HETrans to FC]=””,”DC PDF & HETrans to FC”,
IF([DC Native & HETrans to CenDC]=””,”DC Native & HETrans to CenDC”,
IF([DC copy to Stick File]=””,”DC to File Drg in Stick File”,”Call if you get this message”))))

Iain

31. Steve says:

Awesome! Just what I needed!

32. kanure says:

Hi,

this is simply brilliant,
was very userful for my initiative!
Thanks once again!!

33. Michael says:

Hi Christophe

Very informative blog you have going on here! Thank you for contributing your expertise! I have a problem similarly described by your colors example. Mine is listed below and I would welcome your thoughts as I still get the syntax error message.

=IF(AND(Urgency=”Soon”,Importance=”Critical”),”Quality”,
IF(AND(Urgency=”Soon”,Importance=”Desirable”),”Waste”,
IF(AND(Urgency=”Soon”,Importance=”Essential”),”Quality”,
IF(AND(Urgency=”Soon”,Importance=”Opportunistic”),”Waste”
IF(AND(Urgency=”Extreme”,Importance=”Critical”),”Necessity”,
IF(AND(Urgency=”Extreme”,Importance=”Desirable”),”Deception”,
IF(AND(Urgency=”Extreme”,Importance=”Essential”),”Necessity”,)))))))&
IF(AND(Urgency=”Extreme”,Importance=”Opportunistic”),”Deception”,
IF(AND(Urgency=”Opportunistic”,Importance=”Critical”),”Quality”,
IF(AND(Urgency=”Opportunistic”,Importance=”Desirable”),”Waste”,
IF(AND(Urgency=”Opportunistic”,Importance=”Essential”),”Quality”,
IF(AND(Urgency=”Opportunistic”,Importance=”Opportunistic”),”Waste”,
IF(AND(Urgency=”very”,Importance=”Critical”),”Necessity”,
IF(AND(Urgency=”Very”,Importance=”Desirable”),”Deception”,)))))))&
IF(AND(Urgency=”very”,Importance=”Essential”),”Necessity”,
IF(AND(Urgency=”Very”,Importance=”Opportunistic”),”Deception”,))

Regards

Michael

• Christophe says:

Michael, I think your formula exceeds the number of allowed characters (the exact value must be somewhere on my blog).

34. Kathy says:

Hi Christopher,

Can you help me with this? I’m getting a syntax error. If I remove the end “” I then get the word FALSE attached to the column entry. Ex: HighFALSE or FALSELow.

=IF(AND([Likelihood]=”High”,[Impact]=”High”),”High”,
IF(AND([Likelihood]=”Medium”,[Impact]=”High”),”High”,
IF(AND([Likelihood]=”High”,[Impact]=”Medium”),”Medium”,
IF(AND([Likelihood]=”Medium”,[Impact]=”Medium”),”Medium”,
IF(AND([Likelihood]=”Low”,[Impact]=”Medium”),”Medium”, “”)))))&
IF(AND([Likelihood]=”High”,[Impact]=”Low”),”Low”,
IF(AND([Likelihood]=”Medium”,[Impact]=”Low”),”Low”,
IF(AND([Likelihood]=”Low”,[Impact]=”Low”),”Low”,
IF(AND([Likelihood]=”Low”,[Impact]=”High”),”Medium”, “”))))

I tried scanning through all the above comments but must be missing something.

• Kathy says:

Nevermind …. cut and paste the quotes from Word and that’s what messed it up. Brilliant Blog btw!

35. Edward says:

where is the code generator for this? i am trying to create 50+ if statements.

• Christophe says:

Wow, that’s a lot! You are certainly going to hit the limit for formula length. Anyway, the most recent versions are in the SharePoint User Toolkit at http://usermanagedsolutions.com/SharePoint-User-Toolkit (the formula generator is with the color coding page)

36. Cindy says:

Hi there, PLEASE HELP. Just cannot seem to get this right. Trying to return high, med or low statement based a column with numbers: my formula:
=IF([Impact]=1,”Low”,(IF([Impact]=2,”Medium”,IF([Impact]=3,”High”,””))))
Where am I going wrong, I keep getting a “yes” or “no”.

Please also help with this one: I keep getting a certain answer:

=IF([ProbabilityRating]>0,IF([ProbabilityRating]<50,"Unlikely",IF([ProbabilityRating]<75,"Likely",IF([ProbabilityRating]<=100,"Highly Likely","Certain",””))))

Hope to hear from you soon. Is there anyway to check these formulas in SharePoint Designer

• iain says:

Hi Cindy

In your first example, you have an extra (

Try this:

=IF([Impact]=1,”Low”,IF([Impact]=2,”Medium”,IF([Impact]=3,”High”,””)))

=IF([ProbabilityRating]>0,IF([ProbabilityRating]<50,"Unlikely",IF([ProbabilityRating]<75,"Likely",IF([ProbabilityRating]0

I am sure in every case it will be greater than 0, so you need to think about that as the formula is just following what you have told it to do. Alo note that for each of your statements you have stated the outcome such as Likely etc, but nothing for the first statement.

This is something that you can setup and try in Excel before you put it into Sharepoint.

37. Gretchen Wildrick says:

I’m using this trick to next IF(AND statements where the result is a date. I don’t get any errors with my formula but the result in the column called “Target Due Date” is a number, not a date. I have the field listed as a date type field. When I only had 7 nested statements it worked fine. As soon as I added the & and the other 7 nested statements it ceased displaying as a date? I’m mystified as to how to resolve this.

• Christophe says:

Interesting… A number around 40000, right? Sorry, I’ve never used this to output dates, and I don’t know the reason…

• Gretchen Wildrick says:

I think when I concatenate it is overriding the date format of the field somehow and just giving me the date as a number. If anyone has a solution I’d appreciate it.

38. Anonymous says:

Hi,

In the Script below, if the select the percentage as 100 instead of the first IF condition (Perc>=75), the second IF condition is getting executed i.e the background color for the condition =”75%”,””&Percentage&””,IF(Percentage<="25%","”&Percentage&””,IF(AND(Percentage>”25%”,Percentage<="74%"),"”&Percentage&””)))

Kindly help!!

39. Irmalinda says:

I maintain a Risk Log and I just created a calculated column that will render the ‘Overal Risk Ranking’ based on two columns (Risk Probability and Risk Impact). For Example: If a Risk is classified as being “Very High Probability”, but has a “Low Impact”, the Overall Risk Ranking = Low. I was unable to complete the IF Formula due to character limitation. Anyone have a suggestion to workaround this?

=CONCATENATE(IF(AND([Risk Probability]=”VH”,[Risk Impact]=”VH”),”VH”,IF(AND([Risk Probability]=”VH”,[Risk Impact]=”H”),”H”,IF(AND([Risk Probability]=”VH”,[Risk Impact]=”M”),”H”,IF(AND([Risk Probability]=”VH”,[Risk Impact]=”M”),”H”,IF(AND([Risk Probability]=”VH”,[Risk Impact]=”L”),”L”,IF(AND([Risk Probability]=”H”,[Risk Impact]=”VH”),”H”,IF(AND([Risk Probability]=”H”,[Risk Impact]=”H”),”H”,IF(AND([Risk Probability]=”H”,[Risk Impact]=”M”),”M”,IF(AND([Risk Probability]=”H”,[Risk Impact]=”L”),”L”,IF(AND([Risk Probability]=”M”,[Risk Impact]=”VH”),”H”,IF(AND([Risk Probability]=”M”,[Risk Impact]=”H”),”M”,IF(AND([Risk Probability]=”M”,[Risk Impact]=”M”),”M”,IF(AND([Risk Probability]=”M”,[Risk Impact]=”L”),”L”,IF(AND([Risk Probability]=”L”,[Risk Impact]=”VH”),”H”,IF(AND([Risk Probability]=”L”,[Risk Impact]=”H”),”M”,IF(AND([Risk Probability]=”L”,[Risk Impact]=”M”),”L”,IF(AND([Risk Probability]=”L”,[Risk Impact]=”L”),”L”,IF(AND([Risk Probability]=”VL”,[Risk Impact]=”VH”),”M”,IF(AND([Risk Probability]=”VL”,[Risk Impact]=”H”),”L”,”None”))))))))))))))))))))

• Irmalinda says:

never mind, I used the magic “&” to link up the IFs and I abbreviated the words so I save the number of characters.