6

I have to create a list wherein when user inputs a data to a column the corresponding calculated column should take the data and analyze the condition and then return the value, but my SharePoint online is giving me error when I try to add more than 12 nested if conditions, any workaround please suggest?

Update : This is my formula :

=IF([pn]="S.Vish,ab","[email protected]",IF([pn]="S.Ash,","[email protected]",IF([pn]="S.Kav,ab","[email protected]",IF([pn]="S.Sun,ab","[email protected]",IF([pn]="S.Urb,ab","[email protected]",IF([pn]="S.John,ab","[email protected]",IF([pn]="S.Paw,ab","[email protected]",IF([pn]="S.Ram,","[email protected]",IF([pn]="S.Din","[email protected]",IF([pn]="S.Moh,","[email protected]",IF([pn]="S.Man","[email protected]",IF([pn]="S.Vik,","[email protected]",IF([pn]="S.Vij",,"[email protected]",IF([pn]="S.Pram,ab","[email protected]",IF([pn]="S.Ank,ab","[email protected]",IF([pn]="S.Sai,ab","[email protected]",IF([pn]="S.Ano,ab","[email protected]",IF([pn]="S.Ars,ab","[email protected]",IF([pn]="S.She,ab","[email protected]",IF([pn]="S.Gov,","[email protected]",IF([pn]="S.Nal","[email protected]",IF([pn]="S.San,","[email protected]",IF([pn]="S.Sun","[email protected]","Not OK"))

4 Answers 4

7

A late answer for clarification:

  • Maximum nest IFs: SharePoint 2007 and 2010 only allow 7. SharePoint 2013 and later (including Online) allows 19. Larger values are possible by using batches of 7 or 19 or less and concatenating or adding the results.
  • Maximum formula length: SharePoint 2007 and 2010 only allow 1024 characters. (2013 and later is around one billion!)

The following is works in 2013 and later by using batches of 19 or less. It won't work in 2007 and 2010, even in batches of 7, due to the total length.

if(State="AL","Alabama",
if(State="AK","Alaska",
if(State="AZ","Arizona",
if(State="AR","Arkansas",
if(State="CA","California",
if(State="CO","Colorado",
if(State="CT","Connecticut",
if(State="DE","Delaware",
if(State="FL","Florida",
if(State="GA","Georgia",
if(State="HI","Hawaii",
if(State="ID","Idaho",
if(State="IL","Illinois",
if(State="IN","Indiana",
if(State="IA","Iowa",
if(State="KS","Kansas",
if(State="KY","Kentucky",
if(State="LA","Louisiana",
if(State="ME","Maine",""))))))))))))))))))) &
if(State="MD","Maryland",
if(State="MA","Massachusetts",
if(State="MI","Michigan",
if(State="MN","Minnesota",
if(State="MS","Mississippi",
if(State="MO","Missouri",
if(State="MT","Montana",
if(State="NE","Nebraska",
if(State="NV","Nevada",
if(State="NH","New Hampshire",
if(State="NJ","New Jersey",
if(State="NM","New Mexico",
if(State="NY","New York",
if(State="NC","North Carolina",
if(State="ND","North Dakota",
if(State="OH","Ohio",
if(State="OK","Oklahoma",
if(State="OR","Oregon",
if(State="PA","Pennsylvania",""))))))))))))))))))) &
if(State="RI","Rhode Island",
if(State="SC","South Carolina",
if(State="SD","South Dakota",
if(State="TN","Tennessee",
if(State="TX","Texas",
if(State="UT","Utah",
if(State="VT","Vermont",
if(State="VA","Virginia",
if(State="WA","Washington",
if(State="WV","West Virginia",
if(State="WI","Wisconsin","")))))))))))
5
  • That is a really useful thing you've discovered there Mike, thank you.
    – Tally
    Commented Mar 11, 2022 at 12:31
  • By any chance, would you happen to have a source for those 'nested if function' and 'formula length' limitations? I was referencing this article which mentioned a limit of 7 nested if functions: support.microsoft.com/en-us/office/… Thank You. Commented Apr 11, 2023 at 3:20
  • @user1063287 I think I found that from testing while working on the Calculated Columns book. Commented Apr 12, 2023 at 13:29
  • FYI @MikeSmith-MCT - In SharePoint Online > Microsoft List, I tried to enter a formula in List Validation that was longer than 1024 characters and I got this error: The text you have entered for the Validation Formula must be 1024 characters or fewer, including the leading '=' character. I haven't yet specifically tested the number of nested IF statements List Validation allows there. Commented Apr 12, 2023 at 20:07
  • Validation formulas have different restrictions. I think the maximum nested IFs are the same, but with the obvious length difference. Commented Apr 14, 2023 at 2:01
2

Break up your one Calculated Column into multiple Calculated Columns, then reference the (sub) Calculated Columns in your final result (Calculated Column)

1
  • can you please guide me how to do that ?
    – Vish
    Commented Dec 16, 2017 at 5:06
2

I don't think having more than 12 nested IFs is the problem. Are you grouping your IFs in groups of 7? I have a test list where I added 13 IFs statements and it works.

List with 13 options

Nested IFs

4
  • no i was trying it as a single batch,thanks for this i will try to separate like this and will let you know :)
    – Vish
    Commented Dec 16, 2017 at 9:46
  • I tried this also ,but even if i do as a batch of 7,it is not allowing me to add more than 12 :(
    – Vish
    Commented Dec 16, 2017 at 12:35
  • In this formula you have 7 nested IFs (from Jan till Jul) That is the maximum SharePoint Formulas can handle. @Vish, post your Formula in your question so we can have a look Commented Dec 17, 2017 at 11:50
  • Updated please check
    – Vish
    Commented Dec 18, 2017 at 8:53
1

As a workaround, we can create a single text column to instead of the calculated column, then use SharePoint designer workflow to set the value to the column. SharePoint Designer provide a Graphic User Interface to edit the logic of a workflow, it will be more comfortable to work in a SharePoint Designer workflow if you may have complex logic.

The links below about SharePoint Designer would be helpful:

https://docs.microsoft.com/en-us/sharepoint/dev/general-development/workflow-actions-quick-reference-sharepoint-workflow-platform

https://docs.microsoft.com/en-us/sharepoint/dev/general-development/creating-a-workflow-by-using-sharepoint-designer-and-the-sharepoint-wo

3
  • i tried workflow,but the fact is that ,it seems to be running slow as compared MS-FLOW ,i have return this value to MS-FLOW ,so flow is not waiting for the workflow to run and get the value and the go to next step,instead even before workflow completes it goes next step and then flow falils
    – Vish
    Commented Dec 15, 2017 at 5:07
  • If workflow not meet you, we can also use some JavaScript to set value in New/Edit form page.
    – LZ_MSFT
    Commented Dec 15, 2017 at 6:25
  • can u please guide me how to do that ,i am new to this :)
    – Vish
    Commented Dec 16, 2017 at 5:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.