May 07 2019 10:45 AM
Hello and thank you for your help.
I have a SharePoint List where I want a calculated field to be added where if "product" is A and "platform" is 1, then return the values "11, 12, 13". But I want to add multiples of this.
For example:
If product line is "A" and platform is "1" then 11, 12, 13
If product line is "A" and platform is "2" then 11, 14, 15
If product line is "A" and platform is "3" then 16, 11
If product line is "A" and platform is "4" then 1, 2, 3, 4, 5, 6
If product line is "A" and platform is "5" then 7, 8, 9, 10
I tried this formula but it didn't work...
IF(AND([Product Line]="A",OR([Platform(s)]="1"),11 12 13,IF(AND(
[Platform(s)]="2"),11 14 15,if(and([Platform(s)]="3"),16 11,IF(AND([Platform(s)]="4”),1 2 3 4 5 6,IF(AND([Platform(s)]="5"),7 8 9 10)))
Any help is greatly appreciated. Thank you!
May 07 2019 01:14 PM
@AmyS1996 See Article http://www.viewmaster365.com/functions for all SP functions and syntax.
SharePoint has the limitation of maximum 7 levels deep nested calls.
May 07 2019 01:25 PM
I got this working for you I think per your requirements. I created 3 columns:
I created 5 list items, one to test each different Platform condition. So for each item I chose a different platform then I created my formula. Here it is working:
It can certainly confusing to work with these nasty nested formulas. Here is the column formula:
=IF(AND(ProductLine="A",Platform="1"),"11 12 13",IF(AND(ProductLine="A",Platform="2"),"11 14 15",IF(AND(ProductLine="A",Platform="3"),"16 11",IF(AND(ProductLine="A",Platform="4"),"1 2 3 4 5 6",IF(AND(ProductLine="A",Platform="5"),"7 8 9 10","not applicable")))))
We do not need any OR statements as this is taken care of by the ELSE part of the IF statement. You also weren't wrapping your return value numbers in quotes. The way it works is using nested IF AND statements. We say the first condition IF productline=A AND platform=1, then give 11 12 13. The key is the ELSE.
IF(AND(conditioncol1=value1, conditioncol2=value2), "returned value", ELSE)
You start with the first statement then copy it 4 times and fix the values for the other 4.
IF(AND(ProductLine="A",Platform="2"),"11 14 15",ELSE)
IF(AND(ProductLine="A",Platform="3"),"16 11",ELSE)
IF(AND(ProductLine="A",Platform="4"),"1 2 3 4 5 6",ELSE)
IF(AND(ProductLine="A",Platform="5"),"7 8 9 10","not applicable")
Then paste that 2nd IF statement in the ELSE of the previous one (e.g. paste platform 3 formula in the ELSE of platform 2). Then you keep doing that until you end up with one big fat long statement. For me, it worked first time! Let me know if I misunderstood if you needed something different.
May 27 2020 08:24 AM
I tried this formula for a new project, not sure what is incorrect, as I am unfamiliar with formulas. Is there a better way to write this? (2 Product Lines and several regions and values) Thank you!
=IF(AND(Product Line="A",Region="GLOBAL"),"9",IF(AND(Product Line="A",Region="AN","AM","CJ","EU","INDO"),"5",IF(AND(Product Line="A",Region="ANZ","BRZ","CIS","LAB"),"3",IF(AND(Product Line="A",Region="ASI","N1","N2"),"1",IF(AND(Product Line="B",Region="AN","EU","N1"),"3",IF(AND(Product Line="B",Region="AM","ANZ","ASI","BRZ","CIS","CJ","N2","IND","INDO","GLOBAL","LAB","3","not applicable"))))))