Need formula to calculate automatically most recent avg with new values

[raven_ramon]

Hello Everyone,
I need some help pls:

I have a tab with 52 colunms for each week of the year, followed by column for most recent 8 weeks avg.

Right now after updating each weekly column with new data, i have to manually change the avg function to the most recent 8 columns.

Is there anyway to make the formula automatically pick up most recent weeks?

Thank you for your help.

 

[Steve=True]

If your data goes from b2:ba2, you can use this formula

This formula goes in cell BB2

=AVERAGE(OFFSET(B2,,IF(ISERROR(MATCH(9.999999E+306,Sheet1!B2:BA2)),MATCH("*",Sheet1!B2:BA2,-1),IF(ISERROR(MATCH("*",Sheet1!B2:BA2,-1)),MATCH(9.999999E+306,Sheet1!B2:BA2),MAX(MATCH(9.999999E+306,Sheet1!B2:BA2),MATCH("*",Sheet1!B2:BA2,-1))))-1,1,-8))
Looks for the last value a2:az2 and then backs up 8 cells for the reference range.


Steve=True
www.exceldashboardtemplates.com

 

[T. Valko]

Hello Everyone,
I need some help pls:

I have a tab with 52 colunms for each week of the year, followed by column for most recent 8 weeks avg.

Right now after updating each weekly column with new data, i have to manually change the avg function to the most recent 8 columns.

Is there anyway to make the formula automatically pick up most recent weeks?

Thank you for your help.

Sounds like you want the average of the last 8 entries in the row?

What if there aren't 8 entries?

Where is this data located?

 

[raven_ramon]

Thank you Steve for the speedy reply.
It seems to be working great on the first row
But not when copied down - any reason why?
My data is located in cell ranges H2..BH18 and the avg column for the past 8 wks starts in G2 downwards.
Also, can you just briefly explain to me what are the 9.99999E +306 are doing in the formula?
Thank you so much.

 

[Steve=True]

T. Valko brings up some good points and probably has a more elagant formula for find the last row/column.

You can read all about how this specific formula works in this white paper. Don't worry, it is a quick read

www.grbps.com/Excel7.pdf


Steve=True
www.exceldashboardtemplates.com

 

[raven_ramon]

i think i know why it seems not work:
some of the future values i..e wks 34-52 are linked to another cells and now show up as zeros, so your formula takes into account furture values of zeros into account in the formula
Any way to circumvent this?
Thanks for the pdf - i understand now the 9.9999E+306.

 

[raven_ramon]

Never mind i made the future values an if statement:
if future value = 0,"",future value
and that seems to do the trick.
Thank you!!!!!!

 

[Steve=True]

You are welcome.

Glad it is working for you.

Steve=True
www.exceldashboardtemplates.com