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.
Categories: Sharepoint 2007
Moonis's space 
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.
This should work.
=IF([Days Left]<61,"",IF([Days Left]90,”Green”)))
Sorry, forgot something.
=IF([Days Left]<61,"",IF([Days Left]90,”Green”)))
=IF([Days Left]<61,"",IF([Days Left]90,”Green”)))
something drops off when pasting
Not sure whats going on
IF([Days Left] < 61,"", IF([Days Left] 90,”Green”)))
Here’s my formula:
=IF([Days Left]=”61″:“90”,”Yellow”,”Not Yellow”,IF([Days Left]>=”91″,”Green”,”Not Green”)))
With added spaces:
= IF ( [Days Left] = “61” : “90” , “Yellow” , “Not Yellow” , IF ( [Days Left] > = “91” , “Green” , “Not Green” ) ) )
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”))))
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!
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.
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.
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
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″)))))
the choice filed options for Strategic Alignment column are
“Not Aligned to Business Objectives”
”Moderately aligned to Business Objectives”
”Strategically aligned to Business Objectives”
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 🙂
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.
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??
Hi,
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.
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.
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.
Is the formula that I’m trying to produce possible? 🙂