8 Replies Latest reply on Mar 10, 2016 8:26 AM by Monique Turner

    Calculating 4 week average

    Monique Turner

      Hello all! I think I'm starting to go crazy with this data. My conundrum is this:

       

      I need to calculate two 4 week averages, one using spend from the current week plus the previous 3 weeks, divided by the number of leads from the current week plus the previous 3 weeks, the other is spend from the current week plus the previous 3 weeks divided by the number of qualified leads from the current week plus the previous 3 weeks.

       

      The first 4 week average formula that I have is RUNNING_AVG(Sum([Cost Per Lead])) which is getting me close to, but not the correct number.

      The second 4 week average formula that I have is TOTAL(SUM([Spend]))/TOTAL(Sum([Qualified Leads])) which is not correct either.

       

      I feel like I should be able to do a formula that is essentially Sum([spend]),-3,0))/Sum([qualified leads]),-3,0)) or the sum of the spend of this week and the previous 3 weeks divided by the sum of the qualified leads from this week and the previous 3 weeks.

       

      Help.

       

       

      Thanks in advance!