How to display text rather than a number in a Calculated Field?

Question

I have a List containing a Calculated Field, "Risk Priority", which is calculated using the formula:

=IF( ISBLANK([Internal Control Score])
     ,Impact*Probability
     ,Impact*Probability*[Internal Control Score]
   )

So if no "Internal Control Score" has been entered, "Risk Priority" is the product of "Probability" * "Impact"; if an "Internal Control Score" has been entered, then the "Risk Priority" is reduced accordingly. So far, so straightforward.

However, there is now a business requirement to display the resulting "Risk Priority" as a text value - Low for Risk Priority scores of less than 4, Medium for values of 5 to 12, and High for values over 12. This is the bit I don't know how to do without adding an additional "Display Risk Priority" column to the list. I'd also like to avoid CSR or JSlink.

Answer 1

Try this:

=IF( ISBLANK([Internal Control Score])
     ,IF( Impact*Probability<=4
          ,"Low"
          ,IF( AND(Impact*Probability>4,Impact*Probability<=12)
              ,"Medium"
              ,"High"
             )
        )
     ,IF( Impact*Probability*[Internal Control Score]<=4
         ,"Low"
         ,IF( AND(Impact*Probability*[Internal Control Score]>4
                  ,Impact*Probability*[Internal Control Score]<=12)
             ,"Medium"
             ,"High"
            )
        )
   )

It takes the calculated Risk Priority and based on the criteria you specified in your question, shows Low, Medium or High.

I haven't been able to test this so you may need to do some debugging, but I hope it sets you on the right path.

Answer 2

Note that

Impact * Probability * IF([Internal Control Score],[Internal Control Score] , 1 )

gives you the calculation in one statement