Shorter SharePoint Calculated Column OR Statements

Tested in SharePoint 2010, 2013, 2016 and Online.

Available on Amazon!

Array Constants

Excel will often let you use an array of values where you would normally use a range of cells.

The Excel version:

=VLOOKUP( [StatusCode], 
 {"a","Active";"i","Inactive";"c","Closed"},  , 2, 0 )

The array constant part of the formula is from “{“ to “}” and represents a two dimensional table. The Excel range equivalent to this array looks like this:

   

But…

While that is kind of cool, it does not always work in SharePoint Calculated Columns. One place it does work is with an OR function where it works much like the “IN” keyword in other technologies. The following two functions are equivalent:

=IF( OR( StateCode="OH", StateCode="KY", StateCode="IN", StateCode="IL" ),
     "Central Region", "Other" ) 

=IF( OR( StateCode = {"OH","KY","IN","IL"} ),
     "Central Region", "Other")

(If you see this anywhere other than at TechTrainingNotes.blogspot.com, it was stolen and used without permission!)

Both of the above produce this result:

   

Pretty cool!

Adding Fractions (1/4) to SharePoint Calculated Columns

(The following works SharePoint 2010, 2013, 2016, SharePoint Online, and most likely, 2019!)

0, 1/8, 1/4, 3/8, 1/2, 5/8, 3/4, and 7/8

This is a sample from my soon to be published book “SharePoint Calculated Columns and Validation Formulas”. If you would like to see a lot more about what you can do with Calculated Columns, and maybe win a free book, attend my session at SharePoint Cincy 2018 on April 10th!

In Excel you can get fractions using =TEXT(yourNumberColumn,"# ?/?"):   

The same function in SharePoint will round off the number, and for each of the above values, return 5. If you need fractions then you will need to write your own function to create them. Below we have several versions of this function depending on the fractions and formatting needed.

  

Function to return the fractions 0, 1/4, 1/2, and 3/4

Note: Read to the end to see a shorter version that uses CHOOSE instead of nested IFs.

Just a couple of nested IFs…

=IF( yourNumberColumn - INT(yourNumberColumn) = 0.25,
          INT( yourNumberColumn) & " 1/4",
   IF( yourNumberColumn - INT(yourNumberColumn) = 0.5,
           INT( yourNumberColumn) & " 1/2",
     IF( yourNumberColumn - INT(yourNumberColumn) = 0.75,
              INT( yourNumberColumn) & " 3/4",
         INT(yourNumberColumn) ) ) )

The above formula only works if the entered values end in exactly 0, .25, .5 and .75. If you need your function to round off to the nearest 1/4^th, replace the first value in each IF with:

    ROUND( yourNumberColumn * 4, 0 ) / 4

=IF( ROUND( yourNumberColumn*4, 0 ) / 4 - INT(yourNumberColumn) = 0.25,
     INT(yourNumberColumn) & " 1/4",
   IF( ROUND(yourNumberColumn * 4, 0 ) / 4 - INT(yourNumberColumn) = 0.5,
       INT(yourNumberColumn) & " 1/2",
     IF( ROUND(yourNumberColumn * 4 , 0 ) / 4 - INT(yourNumberColumn) = 0.75,
         INT( yourNumberColumn) & " 3/4",
         ROUND( yourNumberColumn,0) ) ) )

If you would like to see ¼ instead of 1/4 then use the CHAR function to get those symbols. While the ¼, ½, and ¾ characters are available, 1/3 and 1/8 are not.

• Replace " 1/4" with " " & CHAR(188)
• Replace " 1/2" with " " & CHAR(189)
• Replace " 3/4" with " " & CHAR(190)

=IF( ROUND( yourNumberColumn * 4, 0 ) / 4 - INT(yourNumberColumn) = 0.25,
     INT(yourNumberColumn) & " " & CHAR(188),
   IF( ROUND(yourNumberColumn * 4, 0 ) / 4 - INT(yourNumberColumn) = 0.5,
       INT(yourNumberColumn) & " " & CHAR(189),
     IF( ROUND(yourNumberColumn * 4, 0 ) / 4  -INT(yourNumberColumn)=0.75,
         INT( yourNumberColumn) & " " & CHAR(190),
         ROUND( yourNumberColumn, 0 ) ) ) )

Function to return the fractions 0, 1/8, 1/4, 3/8, 1/2, 5/8, 3/4, and 7/8

To add support for eighths we need to add four more IFs. If we change the number used in our rounding code from 4 to 8, and add a few lines, then web can display values to the nearest eighth.

=IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.125,
       INT(yourNumberColumn) & " 1/8",
   IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.25,
         INT(yourNumberColumn) & " 1/4",
     IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.375,
            INT(yourNumberColumn) & " 3/8",
        IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.5,
              INT(yourNumberColumn) & " 1/2",
           IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.625,
                 INT(yourNumberColumn) & " 5/8",
              IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.75,
                    INT(yourNumberColumn) & " 3/4",
                 IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.875,
                      INT(yourNumberColumn) & " 7/8",
    ROUND(yourNumberColumn, 0 ) ) ) ) ) ) ) )

Now we have fractions to the nearest 1/8th.

    

A Much Shorter Version that uses CHOOSE

Here is a much shorter version of a formula to display a number as a fraction:

=ROUNDDOWN( yourNumberColumn + 0.062499, 0 ) &
  CHOOSE( ( ROUND( yourNumberColumn * 8, 0 ) / 8 
            - INT(yourNumberColumn) ) * 8 + 1,
          ""," 1/8"," 1/4"," 3/8"," 1/2", " 5/8", " 3/4", " 7/8",""
        )

This uses CHOOSE to pick the text to display for “less than 1/8^th”, “1/8^th” … “7/8^th” and “more than 7/8^th”. (Nine possible values.)

To get the fraction:

1. Round the number to the nearest 1/8^th:
       ( ROUND( yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) ) * 8
2. And as CHOOSE starts with 1 and not 0, add one to the result:
       ( ROUND( yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) ) * 8 + 1
3. Use CHOOSE to pick the text:
       CHOOSE( … , ""," 1/8"," 1/4"," 3/8"," 1/2", " 5/8", " 3/4", " 7/8","")
4. Concatenate that to the rounded down number:
       ROUNDDOWN( yourNumberColumn + 0.062499, 0 ) & …
   The “+ 0.062499” is added to deal with the last 1/16^th after “7/8^th” so we round up to the next higher number.

.

Adding Roman Numerals to SharePoint

In a Calculated Column

Assuming an integer in a numeric column named the "TheNumber", just add this equation to a Calculated Column:

    =ROMAN(TheNumber)

  

You can also select alternate formats:

   

See: https://support.office.com/en-us/article/ROMAN-function-D6B0B99E-DE46-4704-A518-B45A0F8B56F5

In a Page, Content Editor Web Part or a Rich Text Multiple Lines of Text column.

The Rich Text ribbon offers buttons to create bullets and numbers, but does not have one to select the other list formatting options. With a little HTML edit you can get any of the HTML list styles.

1. Create the numbered list as usual. (1., 2., 3....)
2. Click the "Edit Source" button in the ribbon.
3. Find the <ol> tag and change it to <ol type="I">. (Use a lower case "i" for lower case Roman numerals.)
4. Click OK.

Additional List Styles

When starting with a numbered list you can choose any of the HTML ordered lists (<OL>) types:

• type = "1" – numbers
• type = "A" – Uppercase letters: A, B, C
• type ="a" – Lowercase letters:  a, b, c
• type – "I" – Uppercase Roman numerals: I, II, III
• type = "I" – Lowercase Roman numerals:  i, ii, iii

When starting with a bulleted list you can choose any of the HTML unordered lists (<UL>) types:

• disc
• circle
• square
• none

You can also use your own custom images. See: https://www.w3schools.com/cssref/pr_list-style-image.asp

.

A SharePoint Calculated Column for all 50 States (workarounds for nested IF limits!)

Just in case you might ever need a formula to convert state abbreviations into state names…

• You will need 50 nested IFs,
• but SharePoint 2007 and 2010 only allows 7, and 2013 and later only allows 19.
• You will need a little more than 1300 characters in the formula,
• but SharePoint 2007 and 2010 only allow 1024. (2013 and later are around one billion!)

The trick for the IFs is to only nest 19 at a time and return a state name, or an empty string (""), and then concatenate another 19 nested IFs that return a state name, 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.

But what about the 2007 and 2010 1024 character limit? Renaming the "state" column to just one letter brings the formula down to 1111 characters, but that's still more than the 1024 allowed. Solution? Three Calculated columns. The first has the formulas for the first 25 states (in multiple IF nestings of 7 or less) that returns a state name or an empty string, The second has the next 25 states and returns a state name or an empty string. The third just concatenates the first two columns.

Here's the formula for SharePoint 2013 and later for a column named "State":

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","")))))))))))