0

I created a calculated column in my SharePoint list and everything works fine until I add more than 20 lookup items. Is possible to create a work around to correct this issue? My formulas that work fine are below:

IF([Sample Type]="APP(EM)","4",
  IF([Sample Type]="COND (1-4 samples)","2.5",
    IF([Sample Type]="COND (Acid/Alkali/WFI set)","4",
      IF([Sample Type]="COND (EM)","4",
        IF([Sample Type]="LAL (ENDO)","3",
          IF([Sample Type]="LAL/ENDO (EM)","4",
            IF([Sample Type]="OSMO","2",
              IF([Sample Type]="PCR99","48",
                IF([Sample Type]="5068 rPA-HPLC (Protein A)","9",
                  IF([Sample Type]="pH (1-4 samples)","2.5",
                    IF([Sample Type]="pH (Acid/Alkali/WFI set)","4",
                      IF([Sample Type]="PNA23","4",
                        IF([Sample Type]="pNP (5080)","5",
                          IF([Sample Type]="SA23","12",
                            IF([Sample Type]="SKCS","24",
                              IF([Sample Type]="Titer (HPLC-Platform) Protein A","4",
                                IF([Sample Type]="TOC (EM)","4",
                                  IF([Sample Type]="TOC (Rinse: 1 sample)","2",
                                    IF([Sample Type]="TOC (Rinse: 2 samples)","3"))))))))))))))))))))
Improve this question
3
  • 1
    Break it into multiple calculated field columns
    – Erin L
    Commented Nov 30, 2016 at 19:58
  • Like Erin says.. No one is going to read this. Use a decent text editor to reformat your formula with line breaks and indentations, then indent 4 spaces so when you paste your formula here it is displayed as code
    – Danny '365CSI' Engelman
    Commented Nov 30, 2016 at 20:19
  • In SharePoint 2007 there was a limit of 7 nested IF statements, do not know if it was lifted in recent versions.. I would be surprised as other issues with the Calculated Column have never been fixed
    – Danny '365CSI' Engelman
    Commented Dec 1, 2016 at 11:07

2 Answers 2

Reset to default
1

Use concatenated or added IFs in groups of 7 (for 2007 and 2010) or 19 (for 2013 and later).

The trick for the IFs is to only nest 19 at a time and return either a text value or an empty string (""), and then concatenate another 19 nested IFs that return a text value or an empty string... repeat until done! If you are using 2007 or 2010, then nest 7 at a time, and then concatenate another 7.

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", "" ) ) ) ) ) ) ) &
etc...

For nested IFs that return a number, build the first 19 IFs to return a numeric value or a zero, and the add (+) another 19 IFs, until done.

    if(State="AL", 1,
      if(State="AK", 2,
        if(State="AZ", 3,
          if(State="AR", 4,
            if(State="CA", 5,
              if(State="CO", 6,
                if(State="CT", 7,  0  ) ) ) ) ) ) )   +
    if(State="DE", 8,
      if(State="FL", 9,
etc.

Here's an example that has 50 IFs (all 50 states): http://techtrainingnotes.blogspot.com/2018/03/a-sharepoint-calculated-column-for-all.html

Improve this answer
0

Remove one closing bracket from end ).

Improve this answer

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.