SharePoint 2013 Calculated Column- Evaluate 3 other columns and return non-null value

0
votes

I have three content types on my SharePoint list, each with a calculated value (a number between 0-3) representing the Health of the project for that project type (Health1 is a calculation specific to Project Type 1, Health2 is a calculation specific to Project Type 2, etc.).

Therefore, depending on the Content Type of the list item, only one of these 3 columns will have a number value between 0-3 and the other 2 will always be null.

I want to create a list-level shared calculated column called simply "Health. The formula for "Health" needs to evaluate Health1, Health2 and Health3 and return the number from whichever is not null. Is this possible?

1
sharepoint-2013formulacalculated-columns

1 Answers

0
votes

Because 0 is a FALSE value

=IF( [Health1] , 1 , IF( [Health2] , 2 , IF( [Health3] , 3 , 0 )))

Will return 1,2,3 or 0

.

Hey! Its Friday! Lets add some extra BI fun and add a color indicator for your 0-3 Health level

Create another Calculated Column [HealthStatus]

SET THE DATATYPE TO NUMBER, this makes the HTML work:

Formula:

="<div style=""background:"
&CHOOSE( 
    IF( [Health1] , [Health1] , IF( [Health2] , [Health2] , IF( [Health3] , [Health3] , 0 ))) + 1
    ,"none","lightgreen","yellow","indianred")
&";text-align:center;"">Health "
& [Health]
&"</div>"

In the CHOOSE formula we add 1 to your 0-3 level to select color 1-4

I used no color for the health=0 value

You can use HTML colornames or CSS values. See http://www.w3schools.com/html/html_colornames.asp

Note the use of double-double quotes; so the endresult HTML is like:

<DIV style="background:yellow;text-align:center">Health 2</DIV>

Again; set the datatype to Number so the Calculated Column is actually processed as HTML if you want to check the HTML (maybe a typo somewhere) you can set the datatype to Text, this will display the HTML as text in your View.