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

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

2. Brilliant.

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

4. 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…

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

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

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

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

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

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

10. What a timesaver…. Thanks!

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

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

      Same in the second part, after the ampersand.

12. Rock on! That was it, thanks bro!!

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

14. Pingback: Tutorial: add color coding to your SharePoint 2007 calendar in 15 minutes « Path to SharePoint

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

16. Pingback: b r e n d a n n e w e l l . c o m » Colour coded SharePoint 2010 Calendar

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

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

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

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

21. Pingback: 如何向 sharepoint2007 日历中添加颜色 | CS Magazine

22. This just save my life, thank you so much!

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

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

25. 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)

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

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

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

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

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

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

32. How to make color diplay on Week View?

    • 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

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

34. 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 Upload PDF]=””,”DC to Upload PDF”,
    IF([DC copy to Stick File]=””,”DC to File Drg in Stick File”,”Call if you get this message”))))

    Iain

35. Awesome! Just what I needed!

36. Hi,

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

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

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

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

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

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

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

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

    • Hi Cindy

      In your first example, you have an extra (

      Try this:

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

      In your second question

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

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

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

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

42. Pingback: SharePoint List – Custom Progress Calculated Column | Valy Greavu's Live Blog

43. Pingback: SharePoint List – Custom Progress Calculated Column

44. Pingback: SharePoint Ordering for humans | Lamby and Friends

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

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

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

Comments are closed.