In this article, we’ll be discussing the single function belonging to the “Lookup and Reference” set of SharePoint formulas, the “CHOOSE” function.

Background

Since the formulas available in SharePoint are “Based” on what’s available in Excel (and since this function is part of the “Lookup and Reference” set within Excel as well) they were nice enough to give us a category that matches up with this, but unfortunately only includes the single function and no others (Excel contains 18 functions in this set).

Most of this has to do with the fact that in Excel you can reference any column in any row within your formulas, and the functions contained within this set are designed to do just that, but within SharePoint, formulas can only reference columns in the current row (item) and cannot reference other rows. Because of this, we’re limited to the single “CHOOSE” function since it is designed to be used within the same row (unlike the remaining function in Excel’s list of “Lookup and Reference” functions).

Overview

The function itself, is simply a way for us to specify a number of results beforehand (up to 29), and pick which result to use based on its “index” or numerical position (result1 is in position 1; result2 is in position 2, etc.) in a list of values.

The syntax for this function is in the form of:

=CHOOSE(index_num,value1,value2,value3,…etc.)

“index_num” refers to the numerical position (starting at 1) of the value (result) we need.

“value1…etc.” refers to the specific result occupying the numerical position (up to 29).

A basic example of its usage (the more popular example) involves days of the week.

=CHOOSE(WEEKDAY(MyDate),”Sunday”,”Monday”,”Tuesday”,”Wednesday”,”Thursday”,”Friday”,”Saturday”)

In the above, “MyDate” is a “Date/Time” column. The “WEEKDAY” function looks at the value in the “MyDate” column and returns a numerical value (1-7 with Sunday = 1) representing the day of the week. This value is then passed to the parent “CHOOSE” function which looks at the number then displays the text result based on the position of the number.

In this example, if the “MyDate” field has the value of “12/8/2008″, the function will return a value of “Monday” since December 8^th was indeed a Monday.

Getting Started

Although this is a nice example of its usage, I want to look at something a bit more advanced to illustrate the depth of how to work with the function (in addition to other functions), to determine the values and numerical positions of the data.

The example I’d like to discuss (since I work at a college), is that of a “Grade Book” list (this is also a common example, but we’re going to expand the idea a bit more than what’s normally covered). Through the use of the “CHOOSE” function in addition to several nested “IF-AND” functions (plus a few other familiar functions), we’ll be able to create a working grading system that allows for tallying multiple test scores, point totals, percentages, and a final grade (letter grade – including “plus” and “minus” letters).

To start, in SharePoint create a new “custom list” called “Grade Sheet“.

Once created, change the default “Title” column to “Student Name” (optional, but does help in recognizing the purpose of the data).

Next, we’ll create the “Test” columns where we’ll be entering in the scores for the individual tests. Create each column as a “Numerical” type with all defaults, and use simple column names (i.e. “Test 1“, “Test 2“, “Test 3“, etc.) to make it easier in the formulas later. Additionally, in this example, each test will be worth a maximum of 20 points each, so add in “Min” and “Max” values of “0″ and “20″ for each column.

For this article, I’ll be using 6 columns (”Test 1″ through “Test 6″, but you can create as many as you need.

Next, we need to tally the total points a student has by using a “SUM” function to add all points in the “Test” columns together.

Create a new column called “TPCount“, make it a “Calculated” type with a return data type of “Number”, uncheck “Add to default view” and enter in the following formula:

=SUM([Test 1],[Test 2],[Test 3],[Test 4],[Test 5],[Test 6])

This simply adds together each score entered in the “Test” columns and displays it in another column (we’ll be using this value later).

Next, we need to create the first piece of our actual calculations used in determining the students “Letter grade”.

The Scale

The scale we’ll be using for our grade chart is a common “10-Point based” grading scale based on the following:

Grade Percentage:

A 90%-100%
B 80%-89%
C 70%-79%
D 60%-69%
F 59% and below

For our purposes, we’ll expand it further to include plus (+) and minus (-) letter grades by modifying it to a “3-Point based” system.

A+ 97-100
A 93-96
A- 90-92
B+ 87-89
B 83-86
B- 80-82
C+ 77-79
C 73-76
C- 70-72
D+ 67-69
D 63-66
D- 60-62
F <60

In order to be able to use this in a calculation, we need to determine what the “GPA” (grade point average) is, and use it to specify the ranges of each letter grade.

Create a new column called “”PercentTotal“, make it a “Calculated” type and enter in the following formula:

=VALUE(TEXT((TPCount/(COUNT([Test 1],[Test 2],[Test 3],[Test 4],[Test 5],[Test 6])*20)),”00%”))

Formula Dissection

In the above formula, we’re taking the total points earned by the student and dividing it by the total number of points available (20 points each test). We accomplish the by grabbing the “TPCount” column value (sum of all points student has), then counting the number of “Test” columns that contains a number and multiplying that number by 20 (our max value). We then format it as percentage (in a “text” format) and grab its “number” value.

The “COUNT” function is used to literally count the number of tested values that contain a number. We’ll be discussing this further in future posts covering the “Statistical” set of SharePoint functions.

Using the above, we can create a new entry on our list to validate the formulas we have so far:

Upon saving, we can see our results with a basic percentage:

To validate our formulas so far, we verify each piece of information:

1. Number of tests = 6.
2. At 20 points possible per test, this gives us a max total of 120 points possible.
3. Adding together the total points earned by the student, we get 112 points earned.
4. To get the basic percentage, we take the total points earned and divide it by the total points possible (112/120), which gives us .0933333 (represented as .093 since the default “Automatic” decimal places in SharePoint is 2).

Next, we need to create the logic that sets the ranges each grade letter could cover (i.e. grade of “B” covers 83-86, grade of “C-” covers 70-72, and so on). We’ll do this through the use of nested “IF-AND” statements.

Create a new column called “GradeCalc1“, make it a “Calculated” type and enter in the following formula (pay attention to the very last piece of the formula in quotes):

=IF((PercentTotal/0.1)<6,1,IF(AND((PercentTotal/0.1)>=6,(PercentTotal/0.1)<6.3),2,IF(AND((PercentTotal/0.1)>=6.3,(PercentTotal/0.1)<6.7),3,IF(AND((PercentTotal/0.1)>=6.7,(PercentTotal/0.1)<7),4,IF(AND((PercentTotal/0.1)>=7,(PercentTotal/0.1)<7.3),5,IF((PercentTotal/0.1)>=7.3,”GradeCalc2″))))))

Formula Dissection

Looking through the formula, we can see several repetitive tasks involving “IF” statements.

An “IF” function is in the form of:

=IF(condition_to_check,value_if_true,value_if_false)

Adding in an “AND” function to check for multiple conditions at the same time, it becomes the form of:

=IF(AND(condition1,condition2),value_if_true,value_if_false)

The above allows us to set our logic to include two different conditions where both must evaluate to “True” in order for the entire check to equal “True”.

Moving through the formula, in the first check, we look to see if a single condition evaluates to “True”:

=IF((PercentTotal/0.1)<6,1,value_if_false)

This checks to see if the value of “PercentTotal” divided by “0.1″ is less than 6. If it is, the result is 1. If its not, we move on to the second “IF” statement (the “value_if_false” part) which includes an “AND” function to check for two conditions.

IF(AND((PercentTotal/0.1)>=6,(PercentTotal/0.1)<6.3),2,value_if_false)

This checks to see if the value of “PercentTotal” divided by “0.1″ is greater than or equal to 6, and if the value of “PercentTotal” divided by “0.1″ is less than “6.3″. If both conditions are “True”, the result is 2. If its not, we move on to the third “IF” statement (and so on).

We progress through the rest of the checks until we reach the maximum nesting limit of 7 (SharePoint limit on nested functions), where we call another “Calculated Column” to process the remaining functions (This is the “GradeCalc2″ in quotes at the end of the formula – we’ll come back to this formula and remove the quotes after we create that column).

Since we’ve reached our maximum for “nested” functions, we now create another column to handle the remaining grade ranges.

Create a new column called “GradeCalc2“, make it a “Calculated” type and enter in the following formula:

=IF(AND((PercentTotal/0.1)>=7.3,(PercentTotal/0.1)<7.7),6,IF(AND((PercentTotal/0.1)>=7.7,(PercentTotal/0.1)<8),7,IF(AND((PercentTotal/0.1)>=8,(PercentTotal/0.1)<8.3),8,IF(AND((PercentTotal/0.1)>=8.3,(PercentTotal/0.1)<8.7),9,IF(AND((PercentTotal/0.1)>=8.7,(PercentTotal/0.1)<9),10,IF(AND((PercentTotal/0.1)>=9,(PercentTotal/0.1)<9.3),11,IF(AND((PercentTotal/0.1)>=9.3,(PercentTotal/0.1)<9.7),12,IF((PercentTotal/0.1)>=9.7,13,”No Grade Available”))))))))

Formula Dissection

In the previous column (”GradeCalc1“), the last “IF” statement performs a check where if “True”, calls the second calculated column:

IF((PercentTotal/0.1)>=7.3,”GradeCalc2″)

This allows the overall logic of the entire formula (culmination of both calculated columns) to “flow” through from the first to the second if each of the “Conditional Checks” of the first column each results in “False”.

Because of this, the second column picks up where the first left off by processing the same “Conditional Check” for its first “IF”, then adds in an “AND” to continue with the current grade range to be tested.

=IF(AND((PercentTotal/0.1)>=7.3,(PercentTotal/0.1)<7.7),6,Value_if_false)

With this, we continue on in the same fashion as the previous column by checking for two conditions on each step and moving to the next step if found “False”.

In this first check of this column, the formula processes the data as:

If the value of “PercentTotal” divided by “0.1″ is greater than or equal to 7.3, and the value of “PercentTotal” divided by 0.1 is less than 7.7, return a value of 6. If not, move to the next check.

Adding in the second check:

=IF(AND((PercentTotal/0.1)>=7.3,(PercentTotal/0.1)<7.7),6,IF(AND((PercentTotal/0.1)>=7.7,(PercentTotal/0.1)<8),7,Value_if_false)

The formula expands further and processes the data as:

If the value of “PercentTotal” divided by “0.1″ is greater than or equal to 7.3, and the value of “PercentTotal” divided by 0.1 is less than 7.7, return a value of 6. If not, then if the value of “PercentTotal” divided by “0.1″ is greater than or equal to 7.7, and the value of “PercentTotal” divided by 0.1 is less than 8, return a value of 7.

We continue adding in each additional check until we have the full range of values to test for (combining the two columns into a single formula that enables us to check for the full range of values from 6 to 9.7).

Putting the pieces together

The complete logic of the entire formula (both calculated columns together), resolves to the following steps:

1. If the value of “PercentTotal” divided by “0.1″ is less than 6, return a value of 1.
2. If not, then if the value of “PercentTotal” divided by “0.1″ is greater than or equal to 6 and the value of “PercentTotal” divided by “0.1″ is less than 6.3, return a value of 2.
3. If not, then if the value of “PercentTotal” divided by “0.1″ is greater than or equal to 6.3 and the value of “PercentTotal” divided by “0.1″ is less than 6.7, return a value of 3.
4. If not, then if the value of “PercentTotal” divided by “0.1″ is greater than or equal to 6.7 and the value of “PercentTotal” divided by “0.1″ is less than 7, return a value of 4.
5. If not, then if the value of “PercentTotal” divided by “0.1″ is greater than or equal to 7 and the value of “PercentTotal” divided by “0.1″ is less than 7.3, return a value of 5.
6. If not, then if the value of “PercentTotal” divided by “0.1″ is greater than or equal to 7.3 pass the value of “PercentTotal” to the “GradeCalc2″ column for further processing.
7. If the value of “PercentTotal” divided by “0.1″ is greater than or equal to 7.3 and the value of “PercentTotal” divided by “0.1″ is less than 7.7, return a value of 6.
8. If not, then if the value of “PercentTotal” divided by “0.1″ is greater than or equal to 7.7 and the value of “PercentTotal” divided by “0.1″ is less than 8, return a value of 7.
9. If not, then if the value of “PercentTotal” divided by “0.1″ is greater than or equal to 8 and the value of “PercentTotal” divided by “0.1″ is less than 8.3, return a value of 8.
10. If not, then if the value of “PercentTotal” divided by “0.1″ is greater than or equal to 8.3 and the value of “PercentTotal” divided by “0.1″ is less than 8.7, return a value of 9.
11. If not, then if the value of “PercentTotal” divided by “0.1″ is greater than or equal to 8.7 and the value of “PercentTotal” divided by “0.1″ is less than 9, return a value of 10.
12. If not, then if the value of “PercentTotal” divided by “0.1″ is greater than or equal to 9 and the value of “PercentTotal” divided by “0.1″ is less than 9.3, return a value of 11.
13. If not, then if the value of “PercentTotal” divided by “0.1″ is greater than or equal to 9.3 and the value of “PercentTotal” divided by “0.1″ is less than 9.7, return a value of 12.
14. If not, then if the value of “PercentTotal” divided by “0.1″ is greater than or equal to 9.7, return a value of 13, and of not, display the text “No Grade Available”.

An alternative way to “look” at this series of nested functions is to literally break them down into a “line-by-line” visual format to make it easier to see what’s happening (each line performs a check and returns a value if found true, or if found false moves to the next line). After each resulting value, add in a new line to place the next check on its own line.

As an added bonus to laying out the steps in this “visual” manner, it will also let you know how many trailing “closing parentheses” to add at the end…or at least, in the case of this specific formula it will.

Column “GradeCalc1″:

=IF((PercentTotal/0.1)<6,1
,IF(AND((PercentTotal/0.1)>=6,(PercentTotal/0.1)<6.3),2
,IF(AND((PercentTotal/0.1)>=6.3,(PercentTotal/0.1)<6.7),3
,IF(AND((PercentTotal/0.1)>=6.7,(PercentTotal/0.1)<7),4
,IF(AND((PercentTotal/0.1)>=7,(PercentTotal/0.1)<7.3),5
,IF((PercentTotal/0.1)>=7.3,”GradeCalc2″))))))

6 Lines in the above = 6 trailing parentheses.

Column “GradeCalc2″:

=IF(AND((PercentTotal/0.1)>=7.3,(PercentTotal/0.1)<7.7),6
,IF(AND((PercentTotal/0.1)>=7.7,(PercentTotal/0.1)<8),7
,IF(AND((PercentTotal/0.1)>=8,(PercentTotal/0.1)<8.3),8
,IF(AND((PercentTotal/0.1)>=8.3,(PercentTotal/0.1)<8.7),9
,IF(AND((PercentTotal/0.1)>=8.7,(PercentTotal/0.1)<9),10
,IF(AND((PercentTotal/0.1)>=9,(PercentTotal/0.1)<9.3),11
,IF(AND((PercentTotal/0.1)>=9.3,(PercentTotal/0.1)<9.7),12
,IF((PercentTotal/0.1)>=9.7,13,”No Grade Available”))))))))

8 Lines in the above = 8 trailing parentheses.

So again, because of the limits on how deep we can nest functions (SharePoint-imposed limit of 7 nested functions), we had to break our logic into two separate pieces that each don’t go over the limit (”GradeCalc1″ has 5 nesting’s – “GradeCalc2″ reaches the max of 7 nesting’s).

Moving back to our list, if you view the existing record we entered earlier, you’ll now see the additional two calculated columns displayed:

Notice how the “GradeCalc1” column displays the name of the “GradeCalc2″ column and the value displayed in the “GradeCalc2″ column. If you look back at our formulas for both of these columns you can see why these values are being displayed.

Since the value in the “PercentTotal” column is “0.93″, we simply plug that into our formula to trace its path through the logic:

(Below is “Pseudocode“, or “simplified” to illustrate the path taken – refer to the full formula for detailed logic)

value = 9.3 (based on “PercentTotal/0.1″ or 0.93/0.1)

Pass this value into our formula (we’re trying to find a result of “TRUE”):

IF value<6 result=1 (FALSE)
IF value>=6 AND value<6.3 result=2 (FALSE)
IF value>=6.3 AND value<6.7 result=3 (FALSE)
IF value>=6.7 AND value <7 result=4 (FALSE)
IF value>=7 AND value <7.3 result=5 (FALSE)
IF value>=7.3 AND value<7.7 result=6 (FALSE)
IF value>=7.7 AND value<8 result=7 (FALSE)
IF value>=8 AND value <8.3 result=8 (FALSE)
IF value>=8.3 AND value 8.7 result=9 (FALSE)
IF value>=8.7 AND value 9 result=10 (FALSE)
IF value>=9 AND value 9.3 result=11 (FALSE)
IF value>=9.3 AND value 9.7 result=12 (TRUE)
IF value>=9.7 result=13 (Skipped)
“No Grade Available” (Skipped)

Since 9.3 is greater than or equal to 9.3, and is also less than 9.7, our twelfth check evaluates to “True” which displays a result of 12.

So now that we have our value representing the actual grade value, what do we do with it?

In all reality we could simply modify our formula to display the grade letter instead of a number, but since the point of this article is how to use the “CHOOSE” function, we’re going to create another calculated column to handle displaying the grade letters that way.

Before we do this however, we do need to go back and modify the “GradeCalc1” formula to remove the quotes from the call to the “GradeCalc2” column so it will actually pass the value.

To do this, go into the list settings page and open the “GradeCalc1″ column. In the formula, remove the quotes from the very end of the formula where it says “GradeCalc2” then click “OK”.

Question – do you need to put brackets [] around the name of the column?

Answer – no, because the column name is a single word with no spaces and does not reference a “reserved” or “system” name.

Now that we have our formula updated, going back to view the list will show how the values are now displayed.

Note that the results of the “GradeCalc2″ column are now displayed in the “GradeCalc1″ column as well. This is important in how our “CHOOSE” formula will work, which we’ll create next.

To display the grade letters, create a new column called “Final Grade“, make it a “Calculated” type and enter in the following formula:

=CHOOSE(GradeCalc1,”F”,”D-”,”D”,”D+”,”C-”,”C”,”C+”,”B-”,”B”,”B+”,”A-”,”A”,”A+”,”No Grade Available”)

Formula Dissection

The “CHOOSE” function is in the form of:

=CHOOSE(index_num, value1, value2, value3…value29)

“index_num” refers to which of the values to be displayed from 1 to 29.

Since we’re using grade letters as the values to display, our “index_num” values are as follows:

“index_num” 1 = “F”
“index_num” 2 = “D-”
“index_num” 3 = “D”
“index_num” 4 = “D+”
“index_num” 5 = “C-”
“index_num” 6 = “C”
“index_num” 7 = “C+”
“index_num” 8 = “B-”
“index_num” 9 = “B”
“index_num” 10 = “B+”
“index_num” 11 = “A-”
“index_num” 12 = “A”
“index_num” 13 = “A+”
“index_num” 14 = “No Grade Available”

Notice how this matches up to our previous (”GradeCalc1” and “GradeCalc2“) formulas and the grade scale we’re basing the ranges on?

Going back to our list we can see how the grade letter is displayed after we save the new “Final Grade” column.

To test the functionality of our formulas, create several more items on the list with various test scores to see how the results are displayed.

Note how each range displayed in the “PercentTotal” column matches up with our ranges detailed in the grade scale to reflect an accurate grade letter.

Also, notice (in my example) the “No Grade Available” displayed in the “GradeCalc2” column for “Susan Most”. This is actually performing the calculation exactly as it should because *in the formula for that column, the value listed in the “PercentTotal” column does not fall within any of the “Conditional” checks, so it falls within the last part of the calculation which is the equivalent of “If all previous checks fail, display ‘No Grade Available’”. I added this piece in order to not display the default of “False” for the conditional check, and although it’s not really necessary, I always try and display “Friendly text” instead of the defaults…but it is an optional step.

*Note – although we’re forcing the logic of our entire formula to progress through the first calculated column then move to the second calculated column, both columns are actually processing the results at the exact same time since each column’s formula uses “PercentTotal/0.1″ as its “test” value. By using only the returned value of the first column in our final “CHOOSE” formula, we’ll actually be getting the value from the second calculated column if the “test” value falls within its ranges of checks – this occurs because the first columns “calls” the second column if needed, then waits for it to return a value.

Now that we have our results being displayed, we should do a bit of “Cleanup” in order to only display relevant information on the list, and add in just a bit more functionality to make it easier to read.

To do this, let’s create a couple more columns to help in reading the information being displayed.

Create a new column called “Percent of Total“, make it a “Calculated” type and add in the following formula:

=PercentTotal*100&”/100%”

Formula Dissection

This column simply gives us a more informative view of the percentage each test score results in by taking the value displayed in the “PercentTotal” column multiplied by 100 (making it a whole number) then “Concatenating” it with the text “/100%” to display it in a “fraction format” (i.e. 67/100%).

Next, create another new column called “Points Total” make it a “Calculated” type with a return type of “Number” and enter in the following formula:

=SUM([Test 1],[Test 2],[Test 3],[Test 4],[Test 5],[Test 6])&”/”&(COUNT([Test 1],[Test 2],[Test 3],[Test 4],[Test 5],[Test 6])*20)

Formula Dissection

This column will display the “total points earned” out of the “total points possible” for all tests combined. This is accomplished by simply adding (”SUM”) the earned points of each test, “Concatenating” it with a “/” (for display purposes), then finally “Concatenating” to the end, a “COUNT” of how many “Test” columns contain a number (as in, how many tests have been graded so far) multiplied by the total number of points available for each test (in this case 20 each).

To display similar results for tests that each have different max points (i.e. “Test 1″ is worth 20 points, “Test 2″ is worth 50 points, “Test 3″ is worth 45 points, etc.), you can just manually add in the total to this formula omitting the “COUNT” function and multiplication, or build out a separate formula to handle each test individually.

Once saved, we’ll have a display of “Total Earned / Total Possible” to aid in viewing the data.

Final Steps

Now that we have all our columns created, the final step is to modify our view (or create a new one) to only show the columns relevant, and in an order that makes sense.

In our view click the dropdown to “Modify this view” and make the following changes:

Set the “Columns” section to display the columns in the following order (unchecking any additional columns checked):

1: “Student Name (linked to item with edit menu)”
2: “Test 1″
3: “Test 2″
4: “Test 3″
5: “Test 4″
6: “Test 5″
7: “Test 6″
8: “Points Total”
9: “Percentage of Total”
10: “Final Grade”

Once saved, the display is now more meaningful and relevant to the information needed.

Conclusion

What we’ve accomplished is a way in which to automate the calculation of grades while providing useful information to display the results and “base data” (total points earned/possible, current percentages) used to calculate the grades. Because of the detail in which we wanted to make the letter-grades available (plus and minus grades), we’ve also looked at the concept of linking multiple calculated columns together in order to build more complex formulas, and get around some of the limits imposed by the system.

Granted (as mentioned earlier), we could’ve simply made our nested conditional checks (”IF-AND” statements) result in the “letter grades” themselves instead of resulting in number values that get read by another calculation, but by using “CHOOSE” to process these values, we’ve opened up a new manner in which to process what gets displayed in the end…and in the end, the “Choice” (pun intended) is yours in which one works best for you.

Till next time…

- Dessie

Suggestions for future posts on calculated columns are always welcome, and in fact are encouraged.

Some of the best scenarios to illustrate are the “real-world” problems that we each face day to day, so if you have an example, an idea you want to explore, or a “Can this be done with a Calculated Column?” question that I can use as the topic of a future post, please submit it as a comment below and I’ll see what I can do to work up a post covering it.