Customer Forum

Using a conditional average formula

Workbooks Support Posted: 2013-02-14 09:33

When reporting you may wish to find the average of values returned from a conditional statement.

Using an Opportunity report as an example, we will try to find the average value of each User's Opportunities currently at 'Stage 3 - Closing'.

Using the formula below (wrapping an IF statement with AVG) will divide the Net Value of Stage 3 Opportunities with the total number of Opportunities, not just records that have returned a positive value.

AVG(IF(opportunity_stage_name = 'Stage 3 - Closing', home_currency_net_value, 0))

To work around this, you will need to re-write the formula to manually work out the average as below:

SUM(IF(opportunity_stage_name = 'Stage 3 - Closing ',home_currency_net_value , 0))/SUM(IF(opportunity_stage_name = 'Stage 3 - Closing', 1, 0))

This is finding the total value of Opportunities at 'Stage 3 - Closing' and dividing this figure by the total number of records at 'Stage 3 - Closing'.

See the below screen shot to demonstrate how each formula has returned different results:

Workbooks Support Posted: Mon, 12.08.2013 - 10:41

In next release of Workbooks, due out in late summer, you can build the same report by undertaking the following steps:

Create a new blank report based on Opportunities. Within the Details view add a column for Assigned to Name, Opportunity Stage Name (Opportunity Stage > Name), Net (home currency) and Opportunity Name.

The details view now shows all Opportunities, who they are assigned to, their Name and other pieces of information. A summary view can be built to carry out the conditional average calculations shown in the post above. To do this:

Select 'Add Summary View' and 'Add grouping column'. Group by 'Assigned to Name' & add a column for Opportunity Name.

The incorrect calculation stated above uses the following formula:

'AVG(IF( PARENT('Name') = 'Stage 3 - Closing', PARENT('Net (home currency)'), 0))'

This will divide the Net Value of Stage 3 Opportunities with the total number of Opportunities, not just records that have returned a positive value. The correct formula to use is:

'SUM(IF( PARENT('Name') = 'Stage 3 - Closing ', PARENT('Net (home currency)'), 0))/SUM(IF( PARENT('Name') = 'Stage 3 - Closing', 1, 0))'

This formula finds the total value of Opportunities at 'Stage 3 - Closing' and divides by the total number of records at 'Stage 3 - Closing', gibing the correct figure.