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

# Calculating 4 week average

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.

• ###### 1. Re: Calculating 4 week average

Hi Monique,

Hope this could help:

WINDOW_SUM( SUM([Spend]), -3, 0 ) / WINDOW_SUM( SUM([Leads]), -3, 0 )

Yours,

Yuri

• ###### 2. Re: Calculating 4 week average

Hi Yuriy! Thanks for your suggestion but unfortunately that formula isnt giving me the correct numbers either. It's getting the last number correct, but I need the previous weeks to be correct also.

• ###### 3. Re: Calculating 4 week average

Hi Monique,

What numbers are you expecting to get?

"Average of Averages" -- as in columns 3 and 6?

Or "Weighted Averages" -- as in columns 4 and 7?

Yours,

Yuri

• ###### 4. Re: Calculating 4 week average

The "weighted averages" numbers are correct from columns 4 and 7 !

How do I do that?

Thank you so much for your help!

• ###### 5. Re: Calculating 4 week average

You could see that the calculations in col 4 and 7

are exactly those mentioned above in my first reply :-)

yours,

yuri

• ###### 6. Re: Calculating 4 week average

Thank you! One more quick question though - is the calculation the same if I need it to calculate for way more rows (roughly 180 more?)

• ###### 7. Re: Calculating 4 week average

Yep, the calculation remains the same.

• ###### 8. Re: Calculating 4 week average

Thank you so much!!