Monday, May 21, 2012

Using Calculated Column with maximum number of conditions(IF).

I am just taking an example in which Status column is an choice column with values Choice1 , choice2 and so on. I am assigning the color value based on the selection of status column value.

The Code is :-

=IF([Status]=”",”Black”,IF([Status]=”Choice1″,”Red”,IF([Status]=”Choice2″,”Gold”,IF([Status]=”Choice3″,”Green”,IF([Status]=”Choice4″,”DarkBlue”,IF([Status]=”Choice5″,”DarkCyan”,IF([Status]=”Choice6″,”DarkRed”,IF([Status]=”Choice7″,”Gray”,”"))))))))
For example, if the [Status] column contains Choice5, the output of the formula will be DarkCyan.

You cannot have more than seven nested IF statements in a calculated column.

But there is a worker process, you can do this by using the "&" symbol. Just concatenate these two formulas.

It will only work when the If statements are not nested. Now , i am giving an example of 15 IF functions...!! Check this...!!

The Code is :-

=IF([Status]=”",”Black”,IF([Status]=”Choice1″,”Red”,IF([Status]=”Choice2″,”Gold”,IF([Status]=”Choice3″,”Green”,IF([Status]=”Choice4″,”DarkBlue”,IF([Status]=”Choice5″,”DarkCyan”,IF([Status]=”Choice6″,”DarkRed”,IF([Status]=”Choice7″,”Gray”,”"))))))))&IF([Status]=”Choice8″,”MediumSlateBlue”,IF([Status]=”Choice9″,”SpringGreen”,IF([Status]=”Choice10″,”MidnightBlue”,IF([Status]=”Choice11″,”Sienna”,IF([Status]=”Choice12″,”SlateGray”,IF([Status]=”Choice13″,”OliveDrab”,IF([Status]=”Choice14″,”Gray”,”")))))))

Yeah...!! Finally the code worked...!!
Check out this one..!!
Posted by at
Labels: , ,

7 comments:

  1. UnknownJanuary 7, 2013 at 6:43 AM

    What if you need to handle an "Other" condition if none of the conditions are met? How would you handle this with this structure?

    ReplyDelete
  2. Kishan SrivastavaJanuary 7, 2013 at 7:15 AM

    Hi Erik,

    For the Other condition,
    IF([Status]="Choice14","Gray","Do nothing is met")..
    In my example i have left blank.!!

    Thanks

    ReplyDelete
  3. AnonymousJuly 24, 2013 at 3:14 AM

    Hello, is it possible to add a column looks like this?=IF([Status]=”Choice12″,”SlateGray”,IF(AND([Status]=”Choice13″,[Status ]="Choice12",”OliveDrab”,IF([Status]=”Choice14″,”Gray”,”"))))
    If i add a formular i'm getting an syntax error. If i delete the If(AND( it works. The problem is i need some IF(AND in my formular. How can i do this?
    I'm very thanksful for every comment.
    Greetings from Germany
    Tiac

    ReplyDelete
  4. AnonymousJuly 24, 2013 at 1:13 PM

    Plagiarsing much?

    http://blog.pathtosharepoint.com/2010/04/12/maximum-number-of-if-statements-in-a-calculated-column/

    ReplyDelete
  5. AnonymousAugust 7, 2013 at 1:46 AM

    how to put 60 if Conditions in Calculated Column

    ReplyDelete
  6. AnonymousFebruary 4, 2014 at 1:21 PM

    I figured this out once, now I need to alter the formula and I can't quite figure out the syntax.

    We have a monthly mileage tracker for our office vehicles. You enter the ending mileage from last year, then the ending mileage each month. It keeps a running tab on how many miles the vehicle was driven.

    =SUM(IF([Jan 13]>0,([Jan 13]-[2012 Ending]),"0"))+(IF([Feb 13]>0,([Feb 13]-[Jan 13]),"0"))+(IF([Mar 13]>0,([Mar 13]-[Feb 13]),"0"))+(IF([Apr 13]>0,([Apr 13]-[Mar 13]),"0"))+(IF([May 13]>0,([May 13]-[Apr 13]),"0"))+(IF([Jun 13]>0,([Jun 13]-[May 13]),"0"))+(IF([Jul 13]>0,([Jul 13]-[Jun 13]),"0"))+(IF([Aug 13]>0,([Aug 13]-[Jul 13]),"0"))+(IF([Sep 13]>0,([Sep 13]-[Aug 13]),"0"))+(IF([Oct 13]>0,([Oct 13]-[Sep 13]),"0"))+(IF([Nov 13]>0,([Nov 13]-[Oct 13]),"0"))+(IF([Dec 13]>0,([Dec 13]-[Nov 13]),"0"))

    However, if you get a new vehicle in the middle of the year...you have "0" entries for previous months. So I need to add another IF to each month's calculation. "If previous month is zero, then the calculation is zero". Otherwise, do the math. How do I add that in? I tried:
    =SUM(IF ([2012 Ending]=0, “0”, IF([Jan 13]>0,([Jan 13]-[2012 Ending]),"0")))...and so on but it didnt work (syntax error).

    What am I missing?

    ReplyDelete
  7. UnknownFebruary 2, 2016 at 3:01 AM

    Simply superb article
    sharepoint online training

    ReplyDelete

Subscribe to: Post Comments (Atom)