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

Categories: Sharepoint 2007
1. 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. 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. April 5, 2011 at 6:16 pm

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

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

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. 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. 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. 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″,)))

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

9. April 15, 2014 at 11:07 pm

the choice filed options for Strategic Alignment column are

10. October 23, 2014 at 1:27 am

Hi All,

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