Wrong Output of Nested IF in SharePoint List

[Vivek_PN]

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 · Accepted solution]

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

[#3]

Thanks Rob! It is working now. :)