Calculating Growing Degrees Days

[ventzitt]

Hi,


My aim is to calculate GDD in excel where in column A I got date and hour (reading taken hourly) and in column B is the outdoor temperature. I need to find the maximum outdoor temp. for that day subtract the minimum outdoor temperature and then divided by 2 and the result should show in the last reading for that day in column C.

It may be easer to understand looking at the data below:


Column A Column B Column C

10/03/2013 16:06 24.4

10/03/2013 17:06 20.5

10/03/2013 18:06 17.5

10/03/2013 19:06 15.8

10/03/2013 20:06 15

10/03/2013 21:06 13.2

10/03/2013 22:06 12.4

10/03/2013 23:06 12.2 Result

11/03/2013 00:06 10.5

11/03/2013 01:06 9.2

11/03/2013 02:06 8.3

11/03/2013 03:06 8

11/03/2013 04:06 7.1

11/03/2013 05:06 8.1

11/03/2013 06:06 6.5

11/03/2013 07:06 7.7


Workbook here: http://sdrv.ms/Yn1bcu

 

[Hui]

Ventzitt


Firstly, Welcome to the Chandoo.org Forums


C2: =IF(DAY(A3)<>DAY(A2),(MAX(IF(DAY($A$2:A2)=DAY(A2),$B$2:B2,""))-MIN(IF(DAY($A$2:A2)=DAY(A2),$B$2:B2,"")))/2,B2) Ctrl+Shift+Enter

Copy down


If you only want to show the Calculated value in Column C use

C2: =IF(DAY(A3)<>DAY(A2),(MAX(IF(DAY($A$2:A2)=DAY(A2),$B$2:B2,""))-MIN(IF(DAY($A$2:A2)=DAY(A2),$B$2:B2,"")))/2,"") Ctrl+Shift+Enter

Copy down


Refer to: https://www.dropbox.com/s/jy2hbl65ulzvyno/EasyWeather.xlsx

 

[ventzitt]

I Cannot thank you enough Hui, I've been wrestling for days trying to find the answer thank you, thank you, thank you ...... you are a true excel ninja

 

[Hui]

Ventzitt


Thanx for the kind comments

Thats why we, Ninja's, get the big bucks


Hui...