Oct 23 2023 10:56 PM
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?
Oct 24 2023 02:02 AM - edited Oct 24 2023 02:13 AM
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)
Oct 24 2023 03:13 AM
Oct 24 2023 02:02 AM - edited Oct 24 2023 02:13 AM
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)