SOLVED

# Wrong Output of Nested IF in SharePoint List

Copper Contributor

# Wrong Output of Nested IF in SharePoint List

Dear Community,

I wrote a nested IF statement for a calculated column as below:

=IF(AND(J1=0;K1=0);"Level 1";IF(OR(J1=20; K1=60);"Level 3";"Level 2"))

Possible values in Column J = 0, 10, 20 and Column K = 0, 10, 40, 60

I found an interesting situation where Excel and SharePoint lists returned different values for the same formula. The logic in Excel is working fine, but somehow SharePoint is only returning the value "Level 2" when the value in column K is 60. Am I making any mistake?

2 Replies
best response confirmed by Vivek_PN (Copper Contributor)
Solution

# Re: Wrong Output of Nested IF in SharePoint List

@Vivek_PN a choice column saves a number as text so you need to use Value to convert it to a number for the calculated column. Try this:

=IF(AND(VALUE(J)=0,VALUE(K)=0),"Level 1",IF(OR(VALUE(J)=20,VALUE(K)=60),"Level 3","Level 2"))

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP UK (and classic 1967 Morris Traveller driver)

# Re: Wrong Output of Nested IF in SharePoint List

Thanks Rob! It is working now. 🙂
1 best response

Accepted Solutions
best response confirmed by Vivek_PN (Copper Contributor)
Solution

# Re: Wrong Output of Nested IF in SharePoint List

@Vivek_PN a choice column saves a number as text so you need to use Value to convert it to a number for the calculated column. Try this:

=IF(AND(VALUE(J)=0,VALUE(K)=0),"Level 1",IF(OR(VALUE(J)=20,VALUE(K)=60),"Level 3","Level 2"))

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP UK (and classic 1967 Morris Traveller driver)