3 Replies Latest reply on May 13, 2016 4:18 PM by Patrick A Van Der Hyde

    Average of running sums

    Astrid Amsallem

      Hi all,

       

      I am trying to create a graph that would show me the average engaged contact per account (where the account priority type= A1, A2 and B)- I want to keep track of the moving average (from month to month). An engaged contact is somebody who responded to a marketing campaign.

      I am running into this issue:

       

      Let's say that an account has 8 contacts, 2 never answered, 4 of them responded in June 2015 and 2 of them responded last month.  This account should show under 6 engaged accounts. Right now, it shows under 2 for "Mar 2016" and 4 for "June 2015".

       

      I need to divide the # of engaged contacts by the total number of accounts ( I want to include the accounts that never got an answer (so where the Responded Date is null)).

       

      I created a running sum of engaged contacts but I am struggling with getting the average (tab highlighted in red). I'd like the final view to look like the tab highlighted in red but I know that the calculation isn't correct.

       

      I uploaded the workbook I am using.

       

      Thank you for your help!

       

      Astrid