How to calculate average temperature using offset function?

[mrxlsx]

Hey,

I have Date field in A column and Temperature in B column. A column goes on till today's date. I have to calculate last seven days' average temperature whenever I open the Excel workbook. If I enter Today's date in C7 (in C7 TODAY function is there), I want the last seven day's average temperature from today to be captured in D7 using OFFSET function. Is it possible? It is a dynamic range and gets updated every day with today's temperature recorded @ 5am Indian Std Time. My office opens @ 10am everyday.

I need to work on something using this average figure.

Thank you
Regards
mrxlsx

 

[Vidar]

Maybe...
=AVERAGE(OFFSET(A1,MATCH(C7,A:A,0)-1,1,-7,))

 

[mrxlsx]

Hey Vidar,

That's nice man...thanks a lot..a lot.

mrxlsx

 

[Vidar]

You're welcome , and thank you for the reply

 

[mrxlsx]

Hello Vidar,


I have another question which is killing me from months,

I have my data in 12 sheets named by the month names and a Summary sheet. I have to summarize the data.


The columns in all the sheets are Product, Qty for 4 regions, Sales for 4 regions, Commission for 4 regions.


Each month Sales varies. That's the problem for me to use consolidate feature.


For detailed information please download the sample file below.


JCB.xlsx - Speedy Share - upload your files here


Regards
mrxlsx