Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Bronze Lounger
    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 read-only 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?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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 (1-12), b2 has the Year (eg 2002):
    Enter the following ARRAY formula (confirm with ctrl-shift-enter 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

  3. #3
    Bronze Lounger
    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.

  4. #4
    Bronze Lounger
    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 clearer-n the separate sheet, there is only one column with the month and year entered as "MM/DD/YY" and formatted as "MMMM-YY" (example: for January 2002, entry is "1/1/2002", and display is "January-02".

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Heating Degree Days (2000)

    Steve,
    Attached is a file-I 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
    Attached Files Attached Files

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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
    Attached Files Attached Files

  8. #8
    Bronze Lounger
    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 "Read-Only" file! I am afraid your formula doesn't work-the HDDs for January total 822. I couldn't find the problem. Care to lok?

  9. #9
    WS Lounge VIP sdckapr's Avatar
    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 read-only file and the other one you are using and I will have a look.

    Steve

  10. #10
    Star Lounger
    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.
    Attached Files Attached Files

  11. #11
    Bronze Lounger
    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.
    Attached Files Attached Files

  12. #12
    Bronze Lounger
    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.

  13. #13
    Bronze Lounger
    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 trick-see the attached-I 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
    Attached Files Attached Files

  14. #14
    Star Lounger
    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.

  15. #15
    WS Lounge VIP sdckapr's Avatar
    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •