none
Sharepoint Calculated Field formula RRS feed

  • 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
    Monday, February 8, 2010 3:59 PM

Answers

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
    Monday, February 8, 2010 4:23 PM
  • 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 Zou-MSFT Tuesday, February 16, 2010 8:41 AM
    Monday, February 8, 2010 4:55 PM
  • 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 dates

    Its 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
    Thursday, July 19, 2012 4:51 AM