Customer Forum

How do I create a Calculated Formula to output different values depending upon certain criteria?

Workbooks Support Posted: 2016-05-12 15:37

There may be instances when you need a report to output certain values based on several criteria, and one IF statement won't do. Rather than creating a formula with several nested IF statements, it is possible to use the CASE statement. This will set out your criteria a lot more clearly, making it easier to read and amend as necessary.

In the following example, we would like to output an appropriate 'Action' that needs to be followed based on the Assigned To and Type of the Case, which would help a Manager know if they need to prompt the Support Team to pick up more cases, or can see that a Case has been escalated.

The formula below will follow this logic:

Scenario	Condition	Output
1.	The 'Assigned to' field contains the value 'Support' and the Case type does not contain the value 'Support'	Awaiting Assignment
2.	The 'Assigned to' field contains the value 'Support' and the Case type does contain the value 'Support'	Assigned
3.	The 'Assigned to' field contains the value 'Management'	Escalated
4.	If it doesn't match the scenarios above	Review

The formula used to do this:

CASE

WHEN  assigned_to_name  = 'Support' AND  type_name = 'Support Call' THEN 'Awaiting Assignment'

WHEN  assigned_to_name  != 'Support' AND  type_name = 'Support Call' THEN 'Assigned'

WHEN assigned_to_name = 'Management'  then 'Escalate'

ELSE 'Review'

END

The results of this Formula: Below shows a small screenshot of how the results of this Formula are presented in this Report.

NOTE: The methodology used here can be transferred to many different scenarios and can be simplified or extended to cover more fields or more possibilities.