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.
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”))
Kartic's Musings on Corporate Information and Knowledge Management 
Cool – very impresive Kartic!
Informative! Keep the juice flowing.
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”)
Fred you are a beautifuk person. THank you for this input.
Thanks Fred. That is infact a good idea.
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.
What about this solution http://www.codeplex.com/iconset ?
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
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.
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!
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.
Problems with Calculated fields
you should note this->
Probleme mit dem Abfragen von
Berechneten Feldern ? ->
http://www.schonebeck.net/?p=5
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″))
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])
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
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”)))
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.
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
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]))
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?
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?
Wow! After all I got a webpage from where I know how to in
fact take valuable data concerning my study and knowledge.