Results 1 to 15 of 23
Thread: Heating Degree Days (2000)

20020925, 09:12 #1
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Heating Degree Days (2000)
In a "Read Only" file, my company has accumulated the daily hi & lo temperatures for several weather stations on a daily basis. In another separate file, I need to calculate the heating degree days for a given month for a given location. I cannot edit the readonly file in any way, but must leave the raw data as is.
For these purposes, assume a heating degree day is equal to the difference between 65 and the average temperature for a given day. If the average temperature on a given day is above 65, it is assumed the heating degree days for that day are zero. Again, I need to calculate the total heating degree days for a given month.
Any ideas?

20020925, 10:11 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Heating Degree Days (2000)
Assume "read Only file" called: "ReadOnlyFile" and the worksheet is named "TempData" with the data ranges have the daily info: A1:A1000 has the dates, B1:b1000 has the Min Temps, C1:C1000 has the Max temps and D1:d1000 has the average temps
In your separate spreadsheet, assume A1 has the month number (112), b2 has the Year (eg 2002):
Enter the following ARRAY formula (confirm with ctrlshiftenter NOT enter, all one line):
=COUNT(IF(([ReadOnlyFile.xls]TempData!$A$2:$A$1000>=DATE(B1,A1,1))*([ReadOnlyFile.xls]TempData!$A$2:$A$1000<DATE(B1,A1+1,1))*([ReadOnlyFile.xls]TempData!$D$2:$D$1000<65),[ReadOnlyFile.xls]TempData!$D$2:$D$1000))
This may be copied down for any number of rows you want.
If You do NOT have a column of "average data" but want to calculate it from (Max+min)/2 then use the formula:
=COUNT(IF(([ReadOnlyFile.xls]TempData!$A$2:$A$1000>=DATE(B1,A1,1))*([ReadOnlyFile.xls]TempData!$A$2:$A$1000<DATE(B1,A1+1,1))*(([ReadOnlyFile.xls]TempData!$b$2:$b$1000 + [ReadOnlyFile.xls]TempData!$c$2:$c$1000)/2<65),[ReadOnlyFile.xls]TempData!$a$2:$a$1000))
Steve

20020925, 10:30 #3
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Heating Degree Days (2000)
Thanks, Steve. I'll give it a try and let you know.

20020925, 10:42 #4
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Heating Degree Days (2000)
Sorry, Steve. I should have been clearern the separate sheet, there is only one column with the month and year entered as "MM/DD/YY" and formatted as "MMMMYY" (example: for January 2002, entry is "1/1/2002", and display is "January02".

20020925, 11:47 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Heating Degree Days (2000)
Change the A1 in the formula to month(a1), change the B1 to year(A1).
Steve

20020925, 18:57 #6
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Heating Degree Days (2000)
Steve,
Attached is a fileI have used 2 worksheet instead of two files, but I still can't get the thing to work. Care to take a stab?
Thanks,
Jeff

20020925, 19:18 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Heating Degree Days (2000)
Here it is.
I don't understand WHY you have individual DAYS on sheet1. Didn't you want the total for the months?? My formula will calculate the SAME total whether it is the 1st, 2nd, 15th or whatever day of the month
I added a list of the months/years and the count of "Heating degree days"
Hope this helps,
Steve

20020925, 19:29 #8
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Heating Degree Days (2000)
The individual days are the same as in the "ReadOnly" file! I am afraid your formula doesn't workthe HDDs for January total 822. I couldn't find the problem. Care to lok?

20020925, 20:11 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Heating Degree Days (2000)
I still don't understand why you need the INDIVIDUAL days on the SUMMARY sheet. You only need the MONTHS since you only want the TOTAL for the month.
Are you summing twice? In the one you attached I get in January, 31 days whether that is Jan 1st, 2nd etc. Are you summing up these sums? It worked fine on the sheet you attached in my subtable.
Post a copy of the readonly file and the other one you are using and I will have a look.
Steve

20020926, 05:34 #10
 Join Date
 Jan 2001
 Location
 Adelaide, South Australia, Australia
 Posts
 85
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Heating Degree Days (2000)
Does the attached do what you need? (I've deleted some lines of data to make the file < 100k for posting)
Ian.

20020926, 07:54 #11
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Heating Degree Days (2000)
Here is the file. The total HDDs for January should be 822, not 31.

20020926, 07:55 #12
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Heating Degree Days (2000)
I will check it out, Ian.
Thanks.

20020926, 08:40 #13
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Heating Degree Days (2000)
Ian,
Looks like you did the tricksee the attachedI have included a couple of "check" columns on the raw data worksheet for the sole purpose of checking the workings of your formulas. They work fine!
By the way, is there any way to accomplish this without resorting to array formulas? I am very unfamiliar with them and how they operate.
Thanks,
Jeff

20020926, 08:56 #14
 Join Date
 Jan 2001
 Location
 Adelaide, South Australia, Australia
 Posts
 85
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Heating Degree Days (2000)
I don't know of any other easy way. However it is worth finding out about array formulas  they are not really that hard to use and often very useful.
There have been discussions about array formulas on this forum before  can someone give us a reference?
All the best,
Ian.

20020926, 10:29 #15
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Heating Degree Days (2000)
Ahh You didn't want the NUMBER of days that had to be heated, you wanted the SUM of the DEGREES that had to be added for the month. My calculation COUNTED the number of days (max for the month would be 31!). I didn't realize that you wanted to SUM the degrees.
I thought you were saying my calcs gave you 822 and I couldn't see how. I didn't realize you WANTED 822 for January!
It seems like you have a solution you can live with, so I won't worry about it any more. If not repost with detailed questions.
FYI,
The solution can be done WITHOUT Arrays IF you use temporary calcs (the array does these temp calcs for you)
In your OTHER workbook (NOT the readonly one) "extract" a column of dates from the readonly data and the HDD for each [=max(0,65 "avgTtemperature"]. Add a new column that calcs the first day of the month. [=date(year(a1), month(a1),1) and copy it down]
Create a summary table with the months/year (1/1/2002, 2/1/2002, etc) and then you can calculate directly using sumif formula. No ARRAYS, but 3 extra columns of data!
With the ARRAYS, you can eliminate the need to get the daily temperature data into your "other spreadsheet" and just have the "summary table' since the ARRAY calculates those temporary columns
Steve