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.
 
Scenario
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.
About these ads
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.

    Cheers,
    Randy.

    • 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

    Amber,
    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:

    =IF(OR([Passport]=”FALSE”,[User]=”FALSE”,[Information]=”FALSE”),””,(IF(OR([Passport]=”TRUE”,[User]=”TRUE”,[Information]=”TRUE”),”Green”,”Yellow”)))

    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”

  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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: