5 Replies Latest reply on Jan 15, 2012 4:44 PM by Dimitri.B

# Average of maximums per year

I have a timeseries of temperatures (measured once every month) at a given location with dates in columns and the temperature in rows. I subdivide the temperatures into years.

Now I'd like to calculate and display the average of maximums per year. Unfortunately, as a Reference Line, I can show the average of all values or the maximum per year, but I can't find how to display the average of maximums.

• ###### 1. Re: Average of maximums per year

What does your data look like?

• ###### 2. Re: Average of maximums per year

This is how the file looks (it's tab-seperated data):

```
Id    StationName    Month    Temperature
10637    Frankfurt/M-Flughafen    01/1991    2.7
10637    Frankfurt/M-Flughafen    02/1991    -1.1
10637    Frankfurt/M-Flughafen    03/1991    8.2
10637    Frankfurt/M-Flughafen    04/1991    9.1
10637    Frankfurt/M-Flughafen    05/1991    11.9
10637    Frankfurt/M-Flughafen    06/1991    15.2
10637    Frankfurt/M-Flughafen    07/1991    21.6
10637    Frankfurt/M-Flughafen    08/1991    21.1
10637    Frankfurt/M-Flughafen    09/1991    17.1
10637    Frankfurt/M-Flughafen    10/1991    9.1
10637    Frankfurt/M-Flughafen    11/1991    4.8
10637    Frankfurt/M-Flughafen    12/1991    1.1
10637    Frankfurt/M-Flughafen    01/1992    2.1
10637    Frankfurt/M-Flughafen    02/1992    3.4
10637    Frankfurt/M-Flughafen    03/1992    6.7
10637    Frankfurt/M-Flughafen    04/1992    9.9
10637    Frankfurt/M-Flughafen    05/1992    16.6
10637    Frankfurt/M-Flughafen    06/1992    18.5
...

```

From the Month column I calculate an actual date by calling DATE("01/" + [Month]).

• ###### 3. Re: Average of maximums per year

Does the attached workbook solve your dilemma?

• ###### 4. Re: Average of maximums per year

Unfortunately, I don't seem to understand the general idea. Here is my workbook.

What I want is a reference line in the graph (much like "Average"), but for the average of maximums (so it should be somewhere along the peaks).

• ###### 5. Re: Average of maximums per year

See your workbook attached, with the solution sheet. Also attached is a short tutorial on how it works.

(Thanks to Joe Mako for his guidance on the best approach).