Running average formula

[shrijan]

Hi all,
i have a situation. I need to calculate the running average for the last 3 weeks in the attached spread sheet. i want the formula to be such that i dont need to update each week as new data are filled in. we are in week 7 at the moment in the attached example. What formula would assist to achieve this?

Regards,
Shrijan

[Ace_XL]

In N2

=AVERAGE(INDEX(B2:L2,COUNT(B2:L2)):INDEX(B2:L2,COUNT(B2:L2)-2))

Copy down

[shrijan]

Hi Ace,

Thanks so much for your prompt response. One quick question, if the average required is for 10 weeks, should i simply change the -2 (in the end) to -10?
S

[Ace_XL]

Thanks so much for your prompt response. One quick question, if the average required is for 10 weeks, should i simply change the -2 (in the end) to -10?

The -2 shd be changed to -9

If you have less than the requisite number of weeks, you'll run into an error. To circumvent that use
=IFERROR(AVERAGE(INDEX(B2:L2,COUNT(B2:L2)):INDEX(B2:L2,COUNT(B2:L2)-2)),"")

[shrijan]

Thanks again. i meant, if there were 52 weeks, and i needed to calculate the running average of the last 10 weeks, and assuming we're in the 11th week, then how would the above formula change?
S

[Ace_XL]

=AVERAGE(INDEX(B2:L2,COUNT(B2:L2)):INDEX(B2:L2,COUNT(B2:L2)-9))

The highlighted number should always be n-1 i.e.

If you need average of 10 weeks, it should be 9
If you need average of 8 weeks, it should be 7
If you need average of 21 weeks, it should be 20

etc

[azumi]

=IF(COUNT(B2:L2);AVERAGE(L2:INDEX(B2:L2;LARGE(IF(ISNUMBER(B2:L2);COLUMN(B2:L2));MIN(3;COUNT(B2:L2)))-COLUMN(B2)+1));"")

Array Formula
Azumi

[shrijan]

hi Ace,
Thanks for this. I am still getting incorrect answers. hence have attached a new spread sheet. what formula do i use in column BE if i need the running average of the last 10 weeks. we are in the 7-13 October week

Shrijan

[Ace_XL]

that's because you have zeroes in all the future weeks. get rid of the zeroes for future weeks and then use

=AVERAGE(INDEX(C3:BC3,COUNT(C3:BC3)):INDEX(C3:BC3,COUNT(C3:BC3)-9))

[shrijan]

Thank you. seems to be working perfectly.
Shrijan

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.