1. ## 30-year averages (2000)

I have weather data dating back to 1965 on a daily basis. What I want to set up and maintain are averages (2-year, 3-year, 4-year, 5-year, 10-year, 15-year, 20-year, 25-year, and 30-year) on a daily basis. However, in setting up my worksheet, I am having trouble trying to deal with leap years-specifically, 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

2. ## Re: 30-year averages (2000)

Jeff

I could interpret you 'issue' in many ways. I'd just like to re-state 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

3. ## Re: 30-year averages (2000)

Jeff, depends how your data is set up, but =DATEDIF(start-date,end_date,"param") handles leap years fine. Maybe all you need to do is specify the exact beginning and end of period dates? =DATEDIF(start-date,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 ignored

4. ## Re: 30-year 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., 2-year, 3-year, 4-year, etc.). However, this is where I run into the leap year problem-that 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

5. ## Re: 30-year 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 (ctrl-shift 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

6. ## Re: 30-year averages (2000)

Steve,
Thanks for your in-depth analyses. Just so I am certain, what I am trying to accomplish is determine the 2-year, 3-year, etc., averages of the highs and low temperatures for each given day of a year. For example, I would like to know the past 2-year average of the high temperature for February 21st, the 3-year average, etc. Will your solution do this?
Thanks again,
Jeff

7. ## Re: 30-year 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 (ctrl-shift-enter 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

8. ## Re: 30-year averages (2000)

Steve,
I can't seem to get this thing to work-here is a sample of my data-would you mind working your magic?
Thanks,
Jeff

9. ## Re: 30-year 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

10. ## Re: 30-year averages (2000)

Steve,
I appreciaye your efforts. However, when I substituted data back to 1990, and included columns on the summary sheet to calculate 10-year 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

11. ## Re: 30-year 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

12. ## Re: 30-year 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 2-year 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 2-year average for 2/23/2003 includes, I believe, the 2/23/2003 data, as well as the 2/23/2002 data. However, the 2-year 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 2-year (and 3-year, 4-year, 5-year, etc.) data that is included in the data worksheet. Make any sense?
Ideas/solutions?
Thanks again,
Jeff

13. ## Re: 30-year averages (2000)

Jeff,
This ARRAY formula (ctrl-shift-enter) 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\$2-1),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\$2-1),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

14. ## Re: 30-year averages (2000)

Steve,
Hate to disagree, but look, for example, at your latest attachment at the entry for 2/24.
Jeff

15. ## Re: 30-year 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

Page 1 of 4 123 ... Last

#### Posting Permissions

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