Zoho Analytics allows you to define your own powerful formulas to meet your specific reporting requirements.

Among the various other in-built formulas available in Zoho Analytics, this segment is about the Nested If function. The Nested If function returns different values for different conditions. They are used to test multiple criteria and take action based on the test results. For example, you can use the Nested If function to categorize: 

Let us now see how to calculate the NPS Value using the  Nested If  function.

You can gauge your customer satisfaction level by analyzing the Net Promoter Score. For the score received on a scale of 0 to 10, you can evaluate the customers contentment by categorizing them as DETRACTORS (0 to 6), PASSIVES (7 or 8), and PROMOTERS (9 or 10) using the Nested If function. By reckoning the number of customers in each category you can estimate the product usability among the customers and the areas of improvement. To do so, 

1. On the toolbar, click Add > Formula Column .

2. Provide a column name in the Formula Column Name field of the Add Formula Column dialog. You can provide a Description as well.

3. The columns in your table and in-built functions will be displayed on the left-side of the dialog. You can click the required columns/functions or enter the required formula inside the Formula field.

The syntax for Nested If function is given below:

If(“Condition1_true”, 'result1',If(“Condition2_true”, ‘result2’, If(“Condition3_true”, 'result3’, 'result4’)))

The Nested If function to categorize your data based on the net promoter score is:

If("Rating"<7, 'DETRACTORS',If("Rating" in (7,8), 'PASSIVES', 'PROMOTERS'))

4. Click Save . The output of the formula adds a new column to the table (a Formula Column). You can use this column to create reports in the same way you use the other columns in the table.

In the similar way, you can perform various other actions using the Nested If function. A few examples include:

The Nested If   function to categorize your customers into various schemes based on the revenue generated:

If("Revenue">15000, 'Platinum',If("Revenue">10000, 'Gold', If("Revenue">5000, 'Silver', 'Others')))

The Nested If function to categorize the support tickets that flow into your portal into shifts based on the created time (hours):

If(hour("Created Time") > 6 and hour("Created Time") < 22 , If(hour("Created Time")>14, 'Second Shift', 'General Shift'), 'Night Shift')