Calculated Columns - Nested If Statements

Question

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.

Answer 1

Hello Priscilla,

Thanks for the detailed information.

From your description and screenshots shared, I understand the problem and I would like to explain the cause of the problem i.e., the reason you got the "No" as result is "there is no value set if the condition is false for your last IF statement".

If I use the formula provided by you, I get the "NO" if the value entered in the column is not true in any of my conditions.

The formula used in Q2:

=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)))

However, the result when you enter the value if the condition is False for the last IF statement:

=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,"")))

Note:

Result:

So in conclusion, the Calculated Formula will give No as a result if you don't mention the value for the condition.

Hope it helps.

Best Regards,

Mia

Answer 2

Thank you Mia for your quick response. I followed your advice and those rows did change from "no" to blank. However, this is not the desired outcome. I need the calculated column to return a "1", "3", or "5", dependent on the answer selected. The resulting numbers will need to be used for further analysis.

As you can see below, it gives accurate results in some columns (Q2), other times it gives accurate results but not for every row.

Answer 3

Hello Priscilla,

Thanks for posting back and updating us.

Generally, IF formula will give you two different outputs based on the condition; if the condition in the IF formula is true, it will give the output added at the true section and if it's false, it will give the output added at the false section.

In your IF formula:

If the answer selected is "Ensuring product is safe is always a top priority Receiving", then you will get an answer as 5

If the answer selected is "It becomes important when something goes wrong", then you will get an answer as 3

If the answer selected is "It's not thought about much", then you will get an answer as 1

However, if it's a Single Line Text column and the user added a different value apart from those three in the above, you will get the output added to the False part of the last IF formula.

After you further update, I assume you are using Choice Column and if the users are able to select one value among those 3, please use the below formula:

=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,1))

But if it's not a Choice Column and the users can enter any value into the column item, then we need to set a specific output when the user entered the value that doesn't match any condition in the formula (Just like how we set blank in the previous reply).

I hope the above information will clear the doubts about the formula.

Appreciate your patience!!

Best Regards,

Mia

Answer 4

Thank you so much for your response! Now this is much closer to the desired outcome, but there's still something amiss. Following your suggestion, a value is populated in the calculated column however, not always the correct one. And to clarify, yes it is a choice column. This is the formula I used:

=IF([How committed are managers (including general mgmt. or other senior staff) to product safety?]="Very committed – e.g., they often talk with all staff about it, it's in our procedures, on notice boards, workplace posters, etc.",5,IF([How committed are managers (including general mgmt. or other senior staff) to product safety?]="Somewhat committed – e.g., they talk about it sometimes at meetings or inspections and audits",3,1))

This is the result (Very committed should return the value of 5 instead of 1 unless I've missed something):

Using the same formula on Q5 column, it returns incorrectly for the value of "3".

Answer 5

Hello Priscilla,

Thanks for the further updates.

I noticed the formula doesn't have any wrong values or info and to double confirm, I tested with the same Choice columns using the same choices used in the formula and I got the accurate result:

So, I suggest you create a new Calculated column and use the above formula to see whether it generates the correct values?

Appreciate your efforts!!

Best Regards,

Mia