1

I'm new to the forum. Nice to be here. Hope someone can help me.

I'm trying to apply the following nested If statement to a calculated column in an SPO list. The formula works fine in Excel (with cell references in place of column references), but I can't get it to be accepted in an SPO calculated column. I've just read that anything above 7 nested If statements need to be broken up before the calculated column will accept them, but however I try I can't get it to be accepted.

If someone can help me with a syntax that works, it will be a massive help.

=IF([Superseded:(on same DWB)]="",IF([Declined: Comments]="",IF([Withdrawn: Comments]="",IF([Pending Approval]="",IF([Compliance Date]="",IF([Dispensation Required?]="",
IF([+/- Days]<-31, "Approved", IF(AND([+/- Days]>=-31,[+/- Days]< 0), "Approaching Expiry", 
IF(AND([+/- Days]>0,[Type of Request]="Breach"),"Ended", "Review Due"))),"Dispensation Required"),"Compliant"),"Awaiting Approval"),"Withdrawn"),"Declined"),"Superseded")

Thanks.

Improve this question
5
  • Are you getting any error while using above formula? Can you try replacing all , in your formula and use ; instead?
    – Ganesh Sanap - MVP
    Commented Dec 5, 2022 at 7:14
  • Thanks for the reply Ganesh. I've tried replacing ',' with ';' but still get the same error message: "Sorry, something went wrong. The formula contains a syntax error or is not supported." As I mentioned, I think it is possibly to do with the fact I have more than 7 nested If statements in the formula. I read that a workaround is to split the If statements into groups of no more than 7, but I can't figure out the syntax for that.
    – RubberDuck
    Commented Dec 5, 2022 at 8:53
  • Which version of SharePoint are you using? As per this thread. In SP 2013/2016/2019 and online, you can use up to 19 nested levels of IF: sharepoint.stackexchange.com/questions/231997/…
    – Ganesh Sanap - MVP
    Commented Dec 5, 2022 at 9:36
  • Thanks Ganesh. I understand that you can have up to 19 nested levels of IF, but the author of the solution in that thread is batching his IFs. What I'm struggling with is the correct syntax to batch the IFs in my formula, so the calculated column accepts it. I'm looking for some help with that.
    – RubberDuck
    Commented Dec 5, 2022 at 9:56
  • check my answer given below. let me know if it works for you.
    – Ganesh Sanap - MVP
    Commented Dec 5, 2022 at 11:57

1 Answer 1

Reset to default
0

Try using calculated formula in below format:

=IF(
    [Superseded:(on same DWB)]="",
    IF(
        [Declined: Comments]="",
        IF(
            [Withdrawn: Comments]="",
            IF(
                [Pending Approval]="",
                IF(
                    [Compliance Date]="",
                    IF(
                        [Dispensation Required?]="",
                        "",
                        "Dispensation Required"
                    ),
                    "Compliant"
                ),
                "Awaiting Approval"
            ),
            "Withdrawn"
        ),
        "Declined"
    ),
    "Superseded"
) &
IF(
    [+/- Days]<-31,
    "Approved",
    IF(
        AND([+/- Days]>=-31,[+/- Days]< 0),
        "Approaching Expiry",
        IF(
            AND([+/- Days]>0,[Type of Request]="Breach"),
            "Ended",
            "Review Due"
        )
    )
)

You can also remove the new lines and spaces from above formula.

Documentation: Introduction to SharePoint calculated formulas and functions

Improve this answer
7
  • Thanks Ganesh. I'm still getting exactly the same error message though unfortunately.
    – RubberDuck
    Commented Dec 5, 2022 at 14:13
  • Update Ganesh - I've realised that one of the column names was spelled slightly incorrectly. Once I updated that it accepted the original full formula - which is great. Unfortunately though it doesn't seem to be processing the IF statements relating to the [+/- Days} column. Could that be down to the name of the column do you think?
    – RubberDuck
    Commented Dec 5, 2022 at 14:58
  • Yes. maybe issues with display name of column using operators (+, /, -). Can you try renaming these column and use new names in formula just to be sure?
    – Ganesh Sanap - MVP
    Commented Dec 5, 2022 at 16:45
  • Still the same problem unfortunately Ganesh. It is setting the calculated column result to 'Review Due', irrespective of the number of days in the '+/- Days' (now Days Difference) column. Frustrating! :-)
    – RubberDuck
    Commented Dec 6, 2022 at 10:56
  • What is the result when no. of days are less than -31?
    – Ganesh Sanap - MVP
    Commented Dec 6, 2022 at 11:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.