Share via

Calculated Columns - Nested If Statements

Anonymous
2022-05-23T19:34:38+00:00

I have a SPO list with 12 calculated columns using the =IF function. 5 of those columns produce the value desired, 7 do not. I've edited those 7 formulas multiple times to only get the same errors. In the picture below, all values in columns Q1 and Q2 are correct, Q3 all incorrect and Q4 and Q5 are partially correct. I've formatted to make it easier to see. Items in red should deliver "1", yellow = "3" and green = "5".

The formula I use for the calculated column (first column example): =IF([How important do you think product safety is in the business?]="Ensuring product is safe is always a top priority Receiving",5,IF([How important do you think product safety is in the business?]="It becomes important when something goes wrong",3,IF([How important do you think product safety is in the business?]="It's not thought about much",1)))

I use the same calculation for each column, but I'm baffled as to why the return is "No" instead of the value in the formula and also mixed returns within the same column. I've tried deleting the column and creating a new one, I've tried copying exactly from the choices used in the column to recreate but I can't find what's causing this issue.

Any help would be appreciated.

Microsoft 365 and Office | SharePoint | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-05-26T18:27:29+00:00

    I created a new calculated column as you suggested, but it still does not return the correct value. Going a step further, I created a new list from scratch, but I get similar results.

    Also, I noticed the column formatting does not always format on the original list. The last 2 entries were not formatted (see below).

    0 comments
  2. Anonymous
    2022-05-29T00:03:34+00:00

    Hello Priscilla,

    Sorry for the delay in the response as I was out of the office due to the weekend.

    As per further updates, the behavior you are encountering is strange because you are not getting the correct results even in the new list, and in this situation, I would like to suggest you contact your IT admin of the organization and create a support ticket from the Office 365 Admin center, where the support team has more resources to troubleshoot the problem further by doing the remote session and collecting the logs.

    Appreciate your patience and efforts so far!! Have a good day!!

    Best Regards,

    Mia

    0 comments