Home > Sharepoint 2007 > Writing Nested IF formula for Calculated Field in Sharepoint 2007

Writing Nested IF formula for Calculated Field in Sharepoint 2007

Some one asked a queston about writing nested IF formula. There was really no documentation available for supported Formulas in Sharepoint Calculated column. I spent some time to answer it to some one and i thought i should share it on my BLOG so it can help others as well.
A Custom List has a Probability column and a Calculated column was required to show the Probability Status. In this case user only had WSS so they only had the option to use a Calculated formula to Show a String value representing the Status. Requirement was Show a Status based on Probability below is some representation of requirement.
0 Never
1-10 Unlikely,
10-40 Possible,
40-70 Likely,
>70 Very Likely,
Solution Calculated Formula
Create a Column with type "Calculated (calculation based on other columns) "  and specify the below formula to achive Nested IF based on Probability column value.
=IF(Probability=0,"Never",(IF(Probability<10,"Unlikely",IF(Probability<40,"Possible",IF(Probability<70,"Likely","Very Likely")))))
Please ask any questions if you have on writing formula.
Categories: Sharepoint 2007
  1. Randy Lewis
    April 5, 2011 at 6:09 pm

    Greetings. I’m a SharePoint newbie who’s wrestling with Calculated columns. What I’m trying to do is have SharePoint return “Red”, “Yellow”, or “Green”, based on a column that calculates Days Left until a deadline. If the number in the column is 60, I want SharePoint to return Red; if the number in the column is between 61 and 90, I want SharePoint to return Yellow; if the number in the column is greater than 91, I want SharePoint to return Green. Here’s what I have so far:

    =IF([Days Left]=”61″:”90″,”Yellow”,”Not Yellow”,IF([Days Left]>=”91″,”Green”,”Not Green”)))

    SharePoint is telling me there’s a syntax error, but I’m not sure where the problem is. I’m using the colon to (try to) capture the range of numbers between 61 and 90 (as in Excel).

    Any guidance you can offer would be greatly appreciated.


    • January 28, 2014 at 1:21 am

      This should work.
      =IF([Days Left]<61,"",IF([Days Left]90,”Green”)))

      • January 28, 2014 at 1:23 am

        Sorry, forgot something.

        =IF([Days Left]<61,"",IF([Days Left]90,”Green”)))

      • January 28, 2014 at 1:24 am

        =IF([Days Left]<61,"",IF([Days Left]90,”Green”)))

        something drops off when pasting

      • January 28, 2014 at 1:24 am

        Not sure whats going on

        IF([Days Left] < 61,"", IF([Days Left] 90,”Green”)))

  2. Randy Lewis
    April 5, 2011 at 6:10 pm

    Here’s my formula:

    =IF([Days Left]=”61″:“90”,”Yellow”,”Not Yellow”,IF([Days Left]>=”91″,”Green”,”Not Green”)))

  3. Randy Lewis
    April 5, 2011 at 6:16 pm

    With added spaces:
    = IF ( [Days Left] = “61” : “90” , “Yellow” , “Not Yellow” , IF ( [Days Left] > = “91” , “Green” , “Not Green” ) ) )

  4. Jamie V
    April 12, 2011 at 10:51 am

    I am trying to get a Status update to go automatically based on a renewal date, but get the following error:

    The formula contains a syntax error or is not supported. at Microsoft.SharePoint.Library.SPRequestInternalClass.AddField(String bstrUrl, String bstrListName, String bstrSchemaXml, Int32 grfAdd)
    at Microsoft.SharePoint.Library.SPRequest.AddField(String bstrUrl, String bstrListName, String bstrSchemaXml, Int32 grfAdd)

    Here is the code I have; any help would be appreciated:=IF(Renewal Date>=Today-30,”Expired”,(IF(Renewal Date=Today,”Active”,IF(Renewal Date<=Today-14,”Renwal Required”))))

  5. Amber
    June 23, 2011 at 11:57 am

    I’m hoping you can help. I’m trying to figure out how to write this formula to work in Sharepoint.
    IF Preferred Resource Source = Contractor OR Strategic AND HR Approved = Yes AND GTO EXEC Approved = Yes AND Funding Finance Approved = Yes Needs to populate field with APP+ID+3500
    This is the start of what I have come up with:
    =IF([Preferred Resource Source]=CONTRACTOR – Contracted or Staff Optimization position,STRATEGIC – Fixed-Bid Resource (Statement of Work[SOW]),(IF[HR Approved]=Yes,IF([Funding Finance Approved]=Yes,IF([GTO EXEC Approved]=Yes))))=”APP”+[ID]+3500
    Thank you!

  6. June 29, 2011 at 10:47 am

    you can only get a calculated value out of a forumla, you can do further assignments with = operator. If you want to perform a calculation and update a colummn, You will have to use a designer workflow OR event handler to update a column value. One more point that calculated columns only update their values when you update the referring column values. You can move the assignment logic in event handler and update the field with APP+ID+3500 etc. only keep If condition forumla as calculated field.

  7. Shakey
    February 11, 2014 at 12:06 pm

    I am looking for some help…

    I am trying to look into adding up all the FALSE results and making the indicator turn RED, however any TRUE results showing GREEN or YELLOW.

    Coding below:


    Any problems with this?

    All I seem to get for my column is all YELLOW indicators.

    All help is appreciated.

  8. Disc
    April 15, 2014 at 11:05 pm

    HI Im trying to figure out the formula for strategic alignment rating column,
    I want it to base its calculation on strategic alignment column.

    =IF(Strategic Alignment=”Not Aligned to Business Objectives”,”0″, IF(Strategic Alignment=”Moderately aligned to Business Objectives”,”50″, IF(Strategic Alignment=”Strategically aligned to Business Objectives”,”100″,)))

    i get en error that incorrect syntax, Please Help

    • April 16, 2014 at 11:55 am

      I validated that this one will work for you. You were missing some brackets around field name and in IF conditions. see below.

      =IF([Strategic Alignment]=”Not Aligned to Business Objectives”,”0″,(IF([Strategic Alignment]=”Moderately aligned to Business Objectives”,”50″,(IF([Strategic Alignment]=”Strategically aligned to Business Objectives”,”100″,”0″)))))

  9. Disc
    April 15, 2014 at 11:07 pm

    the choice filed options for Strategic Alignment column are
    “Not Aligned to Business Objectives”
    ”Moderately aligned to Business Objectives”
    ”Strategically aligned to Business Objectives”

  10. David
    October 23, 2014 at 1:27 am

    Hi All,

    Have a similar conditional formula. Could someone PLEASE help.

    =IF(Countdown=0,”Expired”,(IF(Countdown>7,”Approaching Expiration”,IF(Countdown<7,"In Progress"))))

    Unfortunately it's not working as all the results return 'Approaching Expiration" only. Have no idea what I'm doing wrong???

    Any help would me much appreciated 🙂

    • October 23, 2014 at 4:24 pm

      countdown should be integer/numeric column, It sounds like you have it as single line of text (string), Once you change the data type, Formula will start working. I tested your formula that it works with numeric data type. It always return approaching expriration with single line of text.

  11. David
    October 23, 2014 at 6:31 pm

    Thank you for your response. My ‘Countdown’ column is actually a ‘calculation’ formula; counts the days up-to an an ‘Expiration Date’ column. I’m assuming this is adding to my issue.. Would you know of any work-arounds??

  12. Janella Cockburn
    October 1, 2015 at 7:52 am

    I’ve read several of these posts and I’m still puzzled about this so would be grateful for some help.
    I have a task list. and I want to add a simple BRAG status, I’m not looking for icons at this moment in time just the words would be good.
    The results should reflect the following Scenario: If the Task (Action) status = Complete return Blue.
    If the Task (Action) Status is not equal to complete and Today date is the Due date or greater, return a status =Red, if it is not complete and a Todays date is Due Date minus -2 days return Amber, with Green being the final option

    If I can get this to work then add icons later I’ll be happy, but want to take one step at a time, it is a long time since I used SP.

    • October 1, 2015 at 2:47 pm

      Here is your required formula, I tested in my local virtual machine.

      =IF(Status=”Completed”,”BLUE”,(IF(AND(Status”Completed”,Today>=[Due Date]),”RED”,(IF(AND(Status”Completed”,Today>[Due Date]-2),”YELLOW”,”GREEN”)))))

      Please note that sharepoint may complain about Today usage in formula. I had to create a column with the name Today to add my calculated column. I removed it after i added calculated column with formula.

      • Janella Cockburn
        October 5, 2015 at 5:30 am

        Hi, Thanks for the update, it doesn’t do quite what I wanted, oh and the Today, had no problems with that as I have already have a column in for that one. 🙂

        What I’m trying to get to is ….
        If the action is complete the BRAG should be Blue.
        If the action is Not Completed and is Overdue OR the due date is today – BRAG should be Red
        If the action in Not Completed and today is Due Date minus -2 days return Amber
        If the action is Not Completed and today is Due Date minus -3 days return Green.

        The “Not Completed or other status being used as opposed to Complete are
        Not Started, In Progress, Deferred or Waiting on someone else.

        Would you be able to help again on this I shall carry of trying on the meantime. Many Thanks.

  13. Janella Cockburn
    October 6, 2015 at 11:21 am

    Is the formula that I’m trying to produce possible? 🙂

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: