ShowAll Questionssorted byDate Posted
JimmyC1228

# Custom Summary Formula Average Question

I am trying to generate a report that will average the number of days that an opportunity spends in certain stages of our sales process.  I have fields in each opportunity that calculate the number of days per stage, but the problem that I am running into when generating the report is that many of the opportunities have not reached the final stages.  When I average that column, the report totals the number of days for all opportunities and then divides by the total number of opportunities.  I need it to just divide by the number of opportunities for which there is a value present.  For example, if only two opportunities have reached the final stage and have values of 10 and 14, but there are 8 total opportunities in the report, the report returns an average of  3 instead of 12.  I currently have the fields setup so that they treat blank values as blanks.  Does anyone know if this possible, and if so, how would I do this?  Thanks.
OSJMgr

Create a custom count field for EACH stage you want to calculate an average for as follows:

{!STAGE1COUNT__c}=If(NOT(ISNULL({!DaysforStage1__c})),1,0)

On you report, choose SUM for each of these fields, and choose SUM for each DaysforStage field.  Then create a formula on the report that divides each of the DaysforStage fields by the sum of their respection StageCount fields.

Megan Pietruszka
And then vote for this idea! https://success.salesforce.com/ideaView?id=08730000000BpPYAA0