Answered by:
Sharepoint Calculated Field formula
>
Question

Hi All...
I have a calculated field I want to write a IF condition inside it which should have both AND & OR in same formula.. I have created the formula but I am getting an error
Formula..
=IF(AND(IF(OR(F1="Simple",F2="None",F3="None",F4="N/A")),IF(OR(F5="Test",F6="Test123"))),1,0)
I need that the combination from both condition should be true..
Regards
Answers

The best way to work on formulas for SharePoint Calculated columns that I've found is to just use Excel. It's the same engine. Once you get the syntax and values right, you can just copy the formula over to SharePoint, replace the cell references with column names, and you're good to go.
M.
Marc D Anderson  Sympraxis Consulting LLC  Marc D Anderson's Blog  @sympmarc  jQuery Library for SharePoint Web Services Marked as answer by Lu ZouMSFT Tuesday, February 16, 2010 8:41 AM
All replies

I've never done any calculated fields, but it looks to me like you're not telling the two middle IF statements what to do when they're true or false.
= IF( AND( IF( OR( F1="Simple",F2="None",F3="None",F4="N/A" ),1,0 ), IF( OR( F5="Test",F6="Test123" ),1,0 ) ) ,1,0 )
I spaced them out a little, so it's a little easier to keep track of things. I'd also suggest you get a text editor like Notepad2 that highlights matching brackets to keep track of whether you're inside or outside of a bracket statement.
Here it is as a one liner:
=IF(AND(IF(OR(F1="Simple",F2="None",F3="None",F4="N/A"),1,0),IF(OR(F5="Test",F6="Test123"),1,0)),1,0)
I didn't actually try it, so I don't know if it works, but give it a shot.
Edit: Marc pointed out that the engine is the same as in Excel, which I wasn't aware of. I did a quick test in Excel, and as far as I can tell, it seems to do what you need it to.
Facts are meaningless. You could use facts to prove anything that's even remotely true! Edited by Frode Aarebrot Monday, February 8, 2010 5:03 PM Added a note at the bottom


The best way to work on formulas for SharePoint Calculated columns that I've found is to just use Excel. It's the same engine. Once you get the syntax and values right, you can just copy the formula over to SharePoint, replace the cell references with column names, and you're good to go.
M.
Marc D Anderson  Sympraxis Consulting LLC  Marc D Anderson's Blog  @sympmarc  jQuery Library for SharePoint Web Services Marked as answer by Lu ZouMSFT Tuesday, February 16, 2010 8:41 AM

Hello Marc,
Referring to your response above, are you sure that all of the Excel 2010 formula/functions works in SharePoint 2010?
In Excel we have a function/formula, NETWORKDAYS(StartDate,EndDate,holidays)
and the above funtion returns the number of whole WORKDAYS between two datesIts not working for me when I try to use as a calcuated value for a column/field in SharePoint 2010?
Eg: In SharePoint list I have three fields
StartDate  Datetime, EndDate  Datetime, BusinessDays  Numeric
All I am trying to do is to use this formula "=NETWORKDAYS([StartDate],[EndDate])" as a calcualted value for the field Business Days
 Edited by HPurohit Thursday, July 19, 2012 5:00 AM added example