Forum Discussion
Sharepoint List - Multiple IF AND function in calculated column
TPalmer1695 You are using more than 19 nested IF functions in calculated column formula which is not supported in SharePoint Online.
You will have to break your formula with batches of 19 IF functions and combine them using &. Check these links for more information and examples:
- What is the maximum syntax formulas that I can use in calculated column on my SharePoint list?
- What is the workaround for limit in nested if statements in SharePoint calculated column?
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
ganeshsanapYour workaround definitely works but in my case when condition is fulfilled to has to give a specific number value.
My formula is given below, so per activity there is a rate it has to display.
=IF(Activities="Activity1",1375,IF(Activities="Activity2",1376,IF(Activities="Activity3",875,IF(Activities="Activity4",875,IF(Activities="Activity5",1375,IF(Activities="Activity6",1375,IF(Activities="Activity7",1375,IF(Activities="Activity8",1375,IF(Activities="Activity9)",1125,IF(Activities="Activity10",1125,IF(Activities="Activity11",1125,IF(Activities="Activity12",1125,0))))))))))))&IF(Activities="Activity13",1375,IF(Activities="Activity14",1375,IF(Activities="Activity15",1375,IF(Activities="Activity16",1675,IF(Activities="Activity17",1375,IF(Activities="Activity18",1125,IF(Activities="Activity19",1125,IF(Activities="Activity21",1125,IF(Activities="Activity22",1125,IF(Activities="Activity23",1875,IF(Activities="Activity24",875,IF(Activities="Activity25",1500,0))))))))))))&IF(Activities="Activity26",1500,IF(Activities="Activity27",1500,IF(Activities="Activity28",1500,IF(Activities="Activity29",1500,IF(Activities="Activity30",1500,IF(Activities="Activity31",1875,IF(Activities="Activity32",1875,IF(Activities="Activity33",1875,IF(Activities="Activity34",1875,IF(Activities="Activity35",1875,IF(Activities="Activity36",1875,IF(Activities="Activity36",1625,0))))))))))))&IF(Activities="Activity37",1375,IF(Activities="Activity38",1375,0))
While doing this it adds additional zeros to the actual value as shown below in RATE column and hence the total payout value goes for a toss.
Same is the case with my TIME column I havent pasted the formula for it here as its exactly similar to the RATE calculations just values will be different.
Any help is much appreciated.
Nash007 Try using this once:
=IF(Activities="Activity1",1375,IF(Activities="Activity2",1376,IF(Activities="Activity3",875,IF(Activities="Activity4",875,IF(Activities="Activity5",1375,IF(Activities="Activity6",1375,IF(Activities="Activity7",1375,IF(Activities="Activity8",1375,IF(Activities="Activity9)",1125,IF(Activities="Activity10",1125,IF(Activities="Activity11",1125,IF(Activities="Activity12",1125,0))))))))))))&IF(Activities="Activity13",1375,IF(Activities="Activity14",1375,IF(Activities="Activity15",1375,IF(Activities="Activity16",1675,IF(Activities="Activity17",1375,IF(Activities="Activity18",1125,IF(Activities="Activity19",1125,IF(Activities="Activity21",1125,IF(Activities="Activity22",1125,IF(Activities="Activity23",1875,IF(Activities="Activity24",875,IF(Activities="Activity25",1500,0))))))))))))&IF(Activities="Activity26",1500,IF(Activities="Activity27",1500,IF(Activities="Activity28",1500,IF(Activities="Activity29",1500,IF(Activities="Activity30",1500,IF(Activities="Activity31",1875,IF(Activities="Activity32",1875,IF(Activities="Activity33",1875,IF(Activities="Activity34",1875,IF(Activities="Activity35",1875,IF(Activities="Activity36",1875,IF(Activities="Activity36",1625,0))))))))))))&IF(Activities="Activity37",1375,IF(Activities="Activity38",1375,0))
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.