Results 1 to 15 of 48
Thread: 30year averages (2000)

20030221, 14:34 #1
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
30year averages (2000)
I have weather data dating back to 1965 on a daily basis. What I want to set up and maintain are averages (2year, 3year, 4year, 5year, 10year, 15year, 20year, 25year, and 30year) on a daily basis. However, in setting up my worksheet, I am having trouble trying to deal with leap yearsspecifically, how to handle the 29th of February every 4th year.
Anyone out there ever encounter a similar problem and, if so, how did you address it?
Thanks,
Jeff

20030222, 00:08 #2
 Join Date
 Oct 2002
 Location
 Wellington, Wellington, New Zealand
 Posts
 621
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: 30year averages (2000)
Jeff
I could interpret you 'issue' in many ways. I'd just like to restate the way I view the question
I assume that your concern is that sometimes the number of days vary  today's five year average may have one more than one at another time.
Thus the 'value' is different by one day's data. For comparisons  this may create errors.
Clearly it is easy to calculate the actual number of days between two dates  5 years from today was 22 Feb 98 and Excel will give a result.
The size of the error is roughly 1 day in three years  or roughly 0.1%  if your precision is not this high it is not really worth worrying about
If you required high precision I'd 'normalise' all data  e.g. take total between two dates, divide by actual days, and multiply by 365.25

20030222, 00:17 #3
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: 30year averages (2000)
Jeff, depends how your data is set up, but =DATEDIF(startdate,end_date,"param") handles leap years fine. Maybe all you need to do is specify the exact beginning and end of period dates? =DATEDIF(startdate,end_date,"param") parameters are:
"Y" The number of complete years in the period
"M" The number of complete months in the period
"D" The number of days in the period
"MD" The difference between the days in start_date and end_date. The months and years of the dates are ignored
"YM" The difference between the months in start_date and end_date. The days and years of the dates are ignored
"YD" The difference between the days of start_date and end_date. The years of the dates are ignoredJohn ... I float in liquid gardens
UTC 7ąDS

20030222, 13:15 #4
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: 30year averages (2000)
Andrew and John,
Thanks for both of your replies. Andrew, unfortunately, I do need the precision. John, thanks for your suggestion. I should have been clearer: I currently have the daily data set up in one column beginning on January 1, 1965, and continuing down through 2002. What I originally thought was that I merely add additional columns that would perform the averages I am looking for (i. e., 2year, 3year, 4year, etc.). However, this is where I run into the leap year problemthat is, trying to average where there is a leap year (i. e.,, a February 29th) involved where the other years that I am trying to average with the leap year don't have a February 29th. Maybe my problem is how I set the data up originally. I have no experience whatsoever with Access, so that is out of the question.
Any further thoughts on how I might set the data up in Excel?
Thanks,
Jeff

20030222, 13:44 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: 30year averages (2000)
If I understand correctly you have a column of dates (assume named "Date") and a column of temperatures (Assume named "Temperature") which go from 1/1/1965 to present (you could even have FUTURE dates listed with blanks in temperature)
If you create in column E (for example) a list of DATES (does NOT matter what year, I used 2000 since it was a leap year (You want 2/29 as a date). Format it as "mmm d" (you don't really need the year displayed, it is NOT used
Current year average for each date is given by the array formula (ctrlshift enter to confirm) [all one line]
=avergae(IF((MONTH(Date)=MONTH($E2))*(DAY(Date)=DA Y($E2))*(YEAR(Date)=YEAR(NOW())),Temperature))
This can be copied down the column
The 30 year average:
=average(IF((MONTH(Date)=MONTH($E2))*(DAY(Date)=DA Y($E2))*(YEAR(Date)<=YEAR(NOW()))*(YEAR(Date)>YEAR (NOW())30),Temperature))
The 10 year average:
=average(IF((MONTH(Date)=MONTH($E2))*(DAY(Date)=DA Y($E2))*(YEAR(Date)<=YEAR(NOW()))*(YEAR(Date)>YEAR (NOW())10),Temperature))
Your leap year averages will contain only 25% of the values from other years (eg the 30 year avg will only have 7 values) but it is the average of Feb 29's. You can verify the counts by changing the average to count in the formulas.
You can also use, MIN, MAX, Stdev, etc instead of average if you want other statistics.
Steve

20030222, 17:11 #6
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: 30year averages (2000)
Steve,
Thanks for your indepth analyses. Just so I am certain, what I am trying to accomplish is determine the 2year, 3year, etc., averages of the highs and low temperatures for each given day of a year. For example, I would like to know the past 2year average of the high temperature for February 21st, the 3year average, etc. Will your solution do this?
Thanks again,
Jeff

20030222, 17:52 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: 30year averages (2000)
Yes it should. If you have a list with say column A having dates (from 1/1/1965  2/22/2003)
Low temp in Col B for each date, High temp in Col C and Average temp in Col D
The formulas would work fine. Name the ranges (for example), Date, LowTemp, HighTemp, AvgTemp respectively (ranges must all be the same LENGTH!)
If cell E53 contains the date 2/21/2000 (or any other year). the ARRAY formula (ctrlshiftenter to confirm):
=average(IF((MONTH(Date)=MONTH($E53))*(DAY(Date)=D AY($E53))*(YEAR(Date)<=YEAR(NOW()))*(YEAR(Date)>YE AR(NOW())2),HighTemp))
will give the 2 year average for Feb 21st in your dataset:
this will give the 3 year average:
=average(IF((MONTH(Date)=MONTH($E53))*(DAY(Date)=D AY($E53))*(YEAR(Date)<=YEAR(NOW()))*(YEAR(Date)>YE AR(NOW())3),HighTemp))
Note: you could put the Year in a column heading (eg E1) and use instead:
=average(IF((MONTH(Date)=MONTH($E53))*(DAY(Date)=D AY($E53))*(YEAR(Date)<=YEAR(NOW()))*(YEAR(Date)>YE AR(NOW())E$1),HighTemp))
so if copy to other columns it will automatically change the year by what is in the column heading!
If you want to EXPLICITLy calculate the DAILY AVERAGE of High and Low (it is not one of the columns), you could use:
=average(IF((MONTH(Date)=MONTH($E53))*(DAY(Date)=D AY($E53))*(YEAR(Date)<=YEAR(NOW()))*(YEAR(Date)>YE AR(NOW())E$1),(LowTemp+HighTemp)/2))
Steve

20030223, 01:45 #8
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: 30year averages (2000)
Steve,
I can't seem to get this thing to workhere is a sample of my datawould you mind working your magic?
Thanks,
Jeff

20030223, 13:33 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: 30year averages (2000)
I guess I'm a little confused by your data setup and the data you kept. I didn't understand how I could demonstrate, 1 or 2 year average data when it was ALL 30 years ago?
I made some modifications to show you what I envisioned. I have data from 1998 to present (this is made up data, not real: I changed the dates on your data)
On the second sheet I show you the calcs on how to deal with the past 2 year average and 5 year average.
Note It is VERY calcualtion intensive. You should have calcs to MANUAL (tools  options)
A macro might be a better way than recalc since the file size will be large with all the calcs.
Feb29 has NO 2 last 2 year dates since there hasn't been one for 3 years, but there is a 5 year. Future dates >2/22 have only 4 dates for past 5 years, since in the past 5 years there has only been 4 since THIS year counts. If you want to EXCLUDE this year, the calcs need to be modified.
If I am still off the mark on what you want, please be more specific about your goals what you need.
Steve

20030224, 10:27 #10
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: 30year averages (2000)
Steve,
I appreciaye your efforts. However, when I substituted data back to 1990, and included columns on the summary sheet to calculate 10year averages, your formulas "stick" on 6 datapoints; that is, it doesn't look like your formulas will average more than 6 years worth of data. I am really interested in getting 10, 15, 20 25 and 30 year averages as well as what you have provided.
Any thoughts?
Thanks,
Jeff

20030224, 11:23 #11
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: 30year averages (2000)
Jeff,
something simple taht comes to mind:
Did you change the ranges I defined for the named ranges?
Insert name define
Select date, and make sure it defines ALL the data
Do the same for high, low, etc
Steve

20030224, 12:35 #12
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: 30year averages (2000)
Steve,
That did it! I didn't think of that.
One final question: As the formulae now exist, the current year's data that is included on the attached data worksheet is included in the averages. How would you edit the formulae so that if I extended the data worksheet on out to, say, 12/31/2003, the averages would compute 2year averages on the data that exists. That is to say, assume I have data recorded through 2/23/2003, and have the dates for future data out to 12/31/2003. The 2year average for 2/23/2003 includes, I believe, the 2/23/2003 data, as well as the 2/23/2002 data. However, the 2year average for, say, August 12th would be made up of data from only 8/12/2002. What I would like it to be is made up of the most recent 2year (and 3year, 4year, 5year, etc.) data that is included in the data worksheet. Make any sense?
Ideas/solutions?
Thanks again,
Jeff

20030224, 14:26 #13
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: 30year averages (2000)
Jeff,
This ARRAY formula (ctrlshiftenter) will give the count for the x "previous years" (the current YEAR is NOT included)
So the dates in 2003 will average the 2001 and 2002 for 2 years. the 2003 date will NOT be included (until 2004)
=COUNT(IF((MONTH(Date)=MONTH($A4))*(DAY(Date)=DAY( $A4))*(YEAR(Date)<YEAR(NOW()))*(YEAR(Date)>YEAR(NO W())B$21),Date))
For the the others use:
=AVERAGE(IF((MONTH(Date)=MONTH($A4))*(DAY(Date)=DA Y($A4))*(YEAR(Date)<YEAR(NOW()))*(YEAR(Date)>YEAR( NOW())C$21),INDIRECT(C$3)))
You can have dates with no temps and it will not matter. Excel does NOT count blanks in averages and if they are future dates they will be ignored since they are >NOW() anyway.
Steve

20030224, 14:33 #14
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: 30year averages (2000)
Steve,
Hate to disagree, but look, for example, at your latest attachment at the entry for 2/24.
Jeff

20030224, 17:48 #15
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: 30year averages (2000)
I don't understand.
You disagree with what?
When I put the formulas in, I calculate that 2/24 has a 2 year average of HighTemp 34.5 which is the average of 2/24/2001 (41) and 2/24/2000 (28) temps. The count lists as 2.
Steve