Subscribe Bookmark RSS Feed

Cumulative and Rolling Formula being stopped by missing data

saitcopuroglu

Community Trekker

Joined:

Sep 29, 2014

Hi,

Thanks for the support I had from you and I have had these formulas which help me a lot to understand the trend of data better, however the missing cell values stop the formula computing. Do you have any ideas to eliminate this problem?

CUMULATIVE AVERAGE;

If( Row() == 1,

    :SCORE,

    (:CUMULATIVE SCORE[Row() - 1] * (Row() - 1) + :SCORE) / Row()

)



ROLLING AVERAGE (for last seven days)

If( Row() > 6,

    Mean( :SCORE[Index( Row() - 6, Row() )] )

)

9142_2015-07-01_09-53-10.png9152_2015-07-01_09-53-35.png

1 REPLY
markschahl

Community Trekker

Joined:

Jun 18, 2012

Take a look at this post on efficient moving average algorithm. I use the formula a lot.

Re: Efficient moving average algorithm