How to calculate average temperature using offset function?

[rkspeaks]

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 in advance

RK

 

[kaushik03]

Hello RK,


Your question is not very clear to me.


However, I believe you must have the data(previous dates and temperature) stored somewhere in excel. According to my understanding, you want to refer that data and get the average temperature (for the last 7 dates prior today)


Could you please share that data sample and tell us where exactly that is stored?


Else, consider uploading a sample file.


Kaushik

 

[rkspeaks]

Kaushik,


Thanks for responding.


Here is the file.


http://www.4shared.com/file/S8_tb-HU/WeeklyTemperature.html


Regards

RK

 

[kaushik03]

Hi RK,


I am not able to download your file from the given link. It's coming as exe file.


Can you upload it here?

http://www.speedyshare.com/


Kaushik

 

[rkspeaks]

Kaushik,


Here is it.


http://speedy.sh/jAxaM/WeeklyTemperature.xlsx


RK

 

[rkspeaks]

Kaushik,


I got it man.


"=AVERAGE(OFFSET(A1,MATCH(D2,A:A,0)-1,1,-7,))" .


Thanks for your effort man.

RK

 

[Kevin@Radstock]

Hi rkspeaks


If you have Excel 2007>, you can use AVERAGEIFS.


=AVERAGEIFS(B:B,A:A,">"&C7-7,A:A,"<="&C7)


Kevin

 

[kaushik03]

Hey RK,


It's great that you have solved it...and sorry to reply you so late...got stuck in some work.


However, here is an another approach without OFFSET function.


At D7:


=SUMIFS($B$2:$B$100,$A$2:$A$100,">="&$D$4,$A$2:$A$100,"<="&$C$7)/7


Where C7 = today's date

D4 = =$C$7-6


Here is the file:

http://speedy.sh/JbnrY/WeeklyTemperature.xlsx


Regards,

Kaushik