Kartic's Musings on Corporate Information and Knowledge Management

November 12, 2007

Using Calculated Field – Nested IF Statement in Sharepoint list

Filed under: SharePoint — Tags: , , — kartickapur @ 3:51 am

I was trying to convert this lookup table in the excel sheet to a SharePoint list. Basically, The column C1 (Choice of 1 to E) and C2 (A to E) together determine the value of Column C3 (Let’s say). The lookup table given below provides the reference for the Column C3. 

lookup-table.jpg 

The nested IF statement using the calculated field did the trick.

 IF(OR(AND(C1=”A”,C2=”A”),AND(C1=”A”,C2=“B”), AND(C1=”A”,C2=“C”), AND(C1=“B”,C2=”A”), AND(C1=“B”,C2=“B”), AND(C1=“C”,C2=”A”), AND(C1=”A”,C2=“D”)),”High”,IF(OR(AND(C1=“E”,C2=“C”),AND(C1=“E”,C2=“D”),AND(C1=“E”,C2=“E”),AND(C1=“D”,C2=“D”), AND(C1=“D”,C2=“E”), AND(C1=“C”,C2=“D”), AND(C1=“C”,C2=“E”), AND(C1=“B”,C2=“E”)),”Low”,”Medium”))

Advertisements

23 Comments »

  1. Using Calculated Field – Nested IF Statement in Sharepoint list

    Did you see this post at kartickapur.wordpress.com

    Trackback by Windows Vista News — November 12, 2007 @ 4:15 am

  2. Cool – very impresive Kartic!

    Comment by Anthony — November 13, 2007 @ 4:11 am

  3. Informative! Keep the juice flowing.

    Comment by Bernard Lugay — July 10, 2008 @ 3:02 am

  4. I think you can simplify the formula and reduce the number of nestings by replacing A through E by numerical values 1 to 5 and calculate the sum.
    IF((C1+C2)<5, “High”,IF(C1+C2)<7,”Medium”,”Low”)

    Comment by Fred — July 17, 2008 @ 10:51 am

    • Fred you are a beautifuk person. THank you for this input.

      Comment by John Appleseed — June 5, 2014 @ 6:15 pm

  5. Thanks Fred. That is infact a good idea.

    Comment by kartickapur — July 17, 2008 @ 11:02 pm

  6. I am trying to figure out a formula that would return a column name where the data value in that column equals “yes” from a yes/no choice field. Any suggestions?

    My column names are: Remote Monday, Remote Tuesday, Remote Wednesday, Remote Thursday, Remote Friday. I have the rows under each column setup as a Yes/No Choice. I would like to create another column that has a formula to return any Remote Column Names that are Yes.

    Comment by Hunter — October 17, 2008 @ 8:16 pm

  7. What about this solution http://www.codeplex.com/iconset ?

    Comment by Stefan Costan — October 29, 2008 @ 8:52 am

  8. Hi,
    I am trying to do a nested statement where i have 17 statements to be tested. My statements look like
    =IF(Assigned=”Laura Dennis”,”lds”,IF(Assigned=”Ben King”,”bnk”,IF(Assigned=”Todd Bleeker”,”tdb”,…. and like that I have to assign initilas for 14 more. Since I can’t have more than 7 or 8 statements in my nested query, how can I achieve what I am trying to do…is there any other way I can do it?

    Thanks

    Comment by sweha — April 6, 2009 @ 9:45 pm

    • Hi Sweha,
      If you are looking for a simple out of the box solution, you can probably create another reference list with two columns (Assigned and Value).
      Now you can create a simple workflow on sharepoint designer which gets initiated whenever an item is created or updated. This workflow can assign the value based on the Assigned field. Its very simple to create such workflow in SharePoint Designer. If you are not very sure how to do that, you can look at SharePoint Designer Help or email me back.

      Comment by kartickapur — April 7, 2009 @ 5:57 am

  9. Hey Sweha,
    Guys,

    I had a similar problem, but kartickapurs’ solution for me was not exactly simple out of box, because I don’t have a sharepoint designer and didn’t have time. But I figured out a formula exactly what I was looking for without workflow and stuff like that.
    I used CHOOSE formula.
    Check out if you can figure out a solution with it for you. For me it was simple, because I needed to assign things to numbers. You may need to put your data into columns.
    So our company has branches in several location and each location has a number.
    Number one is the HQ(where I am) and the rest of the location numbers is between 9 and 30.
    What I needed to not type the branch names every time only the simple number.
    Obviously each branch name starting with the name of the company. Loc# is the column with the location numbers.
    So here is my formula:

    =”COMPANY NAME “&CHOOSE(IF([Loc#]>8,[Loc#]-7,”1″),”HQ”,”Singapore”,”Dubai”,”New York”,”London”,”Hong Kong”,”Budapest”,”Szeged”,”Paris”,”Chicago”,”Los Angeles”,”Berlin”,”Sidney”,”Roma”,”Brussels”,”Havana”,”Cairo”,”Luxembourg”,”Stockholm”,”Bern”,”Washington”,”Nassau
    “)

    Hope it helps!

    Comment by Frankslater — April 17, 2009 @ 10:30 am

  10. Would it possible when I select Branch from branches lookup (add new item) in the list, it automatically calculate Region (which should hide in new item) it belongs to and display in the list view.

    Comment by sam — July 23, 2009 @ 8:25 pm

  11. Problems with Calculated fields
    you should note this->
    Probleme mit dem Abfragen von
    Berechneten Feldern ? ->
    http://www.schonebeck.net/?p=5

    Comment by David Schonebeck — October 29, 2009 @ 8:54 pm

  12. I’m trying to combine multiple IF statements and keep getting errors. Here is what I have – is this even possible or is there a better way:

    =IF(OR(Unit=”C2″,Unit=”C3″,Unit=”C6″,Unit=”B3 or CCU”, Unit=”B5ICU”,Unit=”RTBC or D7″,Unit=”M2 Operating Rooms”,Unit=”MG Operating Rooms”, Unit=”M2 CRCU”, Unit=”M2 CVICU”,Unit=”Dialysis”,Unit=”D4ICU”,Unit=”M3 IRCCI”,Unit=”M4″,Unit=”M5″,Unit=”ER – Trauma Room”,Unit=”T Wing Odette Clinics”,Unit=”Central Reprocessing”,Unit=”M2 PACU”,Unit=”Cardiac Cath Labs”,Unit=”Endoscopy”,Unit=”Pharmacy admixture”,Unit=”Sterile Supply Room”,Unit=”Animal Rooms”,Unit=”Tissue Culture Lab”,Unit=”Bronchoscopy Suite”,Unit=”Cytoscopy Suite”,Unit=”Pacemaker Insertion”,Unit=”Dental”),”Group 4″=IF(OR(Unit=”B4ICU”,Unit=”B6 Colpo”,Unit=”C4″,Unit=”C5″,Unit=”D2″,Unit=”D4″,Unit=”D5″,Unit=”F2″,Unit=”K Wing”,Unit=”L Wing”,Unit=”HOAC 3E”,Unit=”HOAC 6E”,Unit=”HOAC 7E”,Unit=”Emerg – Blue Zone”,Unit=”Emerg Green Zone”,Unit=”ER Purple Zone”,Unit=”Emerg Orange Zone”,Unit=”Emerg Triage/Waiting Room”,Unit=”Respiratory Therapy”,Unit=”Clean Linen handling storage”,Unit=”Food prep serve dine”,Unit=”Laboratories”),”Group 3″))

    Comment by Rachel — December 6, 2010 @ 5:54 pm

  13. I am trying to calculate in Column C so that it picks up the date in Column A or Column B, whichever is earliest and if Column A is blank, then Column C picks up the date in Column B. Here is what I am using and it dosnn’t work:

    =IF(OR([Customer Kickoff Meeting]>=[SOW Signed],ISBLANK([SOW Signed]))=”Yes”,[Customer Kickoff Meeting],[Customer Kickoff Meeting])

    Comment by Brad Moses — December 16, 2010 @ 6:00 pm

  14. Hi Kartik

    Thanks for this post, I am certain it will come in handy in due time.

    I am quiet new to SP and have been requested by the business to create an issue tracking site. I have created a custom list site since we have multiple fields that are needed.
    I have three fields; WO Group, WO Category each with multiple selections available through a drop down function.
    How can I create a hierarchical structure, to filter the options on the lower drop down lists based on the category selected on a higher drop down list?

    Looking forward to you help.
    Regards,
    Arjun

    Comment by Arj — February 15, 2011 @ 5:04 am

  15. Can you tell me what is wrong with below statement. Tried to follow the example but still get an error.

    IF(OR(AND([Impact]=5,[Probability]=”90%”),AND([Impact]=5,[Probability]=“70%”),
    AND([Impact]=5,[Probability]=“50%”), AND([Impact]=4,[Probability]=”90%”), AND([Impact]=4,[Probability]=“70%”), AND([Impact]=3,[Probability]=”90%”)),”High”,IF(OR(AND([Impact]=5,[Probability]=“30%”),
    AND([Impact]=5,[Probability]=“10%”),AND([Impact]=4,[Probability]=”50%”),AND([Impact]=4,[Probability]=“30%”), AND([Impact]=”,[Probability]=“70%”), AND([Impact]=3,[Probability]=“50%”), AND([Impact]=3,[Probability]=“30%”), AND([Impact]=2,[Probability]=“90%”), AND([Impact]=2,[Probability]=“70%”), AND([Impact]=2,[Probability]=“50%”),AND([Impact]=1,[Probability]=“90%”)),”Medium”,”Low”)))

    Comment by Terry — May 4, 2011 @ 2:03 pm

  16. Frankslater, you are a genius. My situation is slightly different and I also do not have SharePoint desgner. I run a compliance inspection sharepoint and we gather information based off varies compliance programs which each have their own checklists. Is there a way to concatenate data so that if I select a program in Column 1 (lets say Safety) then the information in Column 2 (drop down preferably) changes based upon what I select.

    For example: Column 1 (Dropdown), I choose Safety; Column 2 (Dropdown), I can now select Checklist 200-5, Checklist 34-6, or Checklist HSC 67-E3
    Next Item/Task: Column 1 I choose Installation Records; Column 2 I can now select Checklist 67-5-1, HIRC 55-GG1, or Checklist X7-SAD3

    Does that make sense? Not sure if I can do it. Right now my site works fine and sometimes we have to fix erroneous selections, so this would be a nicety that would save time.

    Comment by Jason — January 3, 2012 @ 6:25 pm

  17. Hi, I have a simple calculation, mutiply two columns and provide the output in a third. In one of the columns I have numbers as well as text and blanks. I have written two statements which seem to work independently but not together can someone help please
    =IF([Contract Term in Months]=”MTM”,[Monthly Revenue]*1),IF([Contract Term in Months]”MTM”,[Monthly Revenue]*[Contract Term in Months]))
    What I’am trying to do is if the value in the column contract term in months = Text in this case MTM to calculate monthly revenue * 1 and if the contract term in months column is not equal to MTM then get the value by multiplying contract term in months with monthly revenue.
    Thank you in advance

    Comment by Kumar Gumparthy — February 13, 2012 @ 4:54 pm

  18. Hello All
    I figured out my issue, I just had to take out one of the brackets after the first IF statement
    =IF([Contract Term in Months]=”MTM”,[Monthly Revenue]*1,IF([Contract Term in Months]”MTM”,[Monthly Revenue]*[Contract Term in Months]))

    Comment by Kumar Gumparthy — February 13, 2012 @ 5:30 pm

  19. I am trying to work on a formula in sharepoint 2007 – I have two columns with dropdowns
    column 1 column 2
    Payments P01
    P02
    P03
    P04
    WO W01
    W02
    W03
    W04
    W05
    W06
    TT T01
    T02
    T03
    T04
    T05
    T06
    What i want is = if i select Payments in column1, i should get only the data starting with P, if WO then i should get data starting with W and so on. In excel we can do it, but how to do it in sharepoint using calculated cell?

    Comment by RV — May 9, 2012 @ 9:09 am

  20. Hello there! Do you know if they make any plugins to protect against hackers?
    I’m kinda paranoid about losing everything I’ve
    worked hard on. Any recommendations?

    Comment by toronto web hosting — December 21, 2012 @ 2:55 pm

  21. Wow! After all I got a webpage from where I know how to in
    fact take valuable data concerning my study and knowledge.

    Comment by http://printerdown.yolasite.com/ — June 24, 2013 @ 8:25 pm


RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: