0

I have a spreadsheet containing the following values (slightly simplified): Date - day in number - Temperature - Windspeed - Hours of sun. Data example:

01-09-2000 - 01 - 20 - 15 - 9
02-09-2000 - 02 - 20 - 15 - 9
03-09-2000 - 03 - 20 - 15 - 9
....
01-09-2000 - 01 - 20 - 15 - 9
02-09-2000 - 02 - 20 - 15 - 9

etc

I have this for 15 consecutive years of the month september. I want to have a graph showing the following:

  • On the X axis I want all days of september, 1-30.
  • On the Y axis I want the mean for that day, a point/line for every variable (in case of my example 3, temperature, windspeed and hours of sun).

In other words, the graph will contain 3 variable values per day, which are all the mean of the relevant variables. Can anyone help me on my way? I have searched and for example found a subject on pivot tables but AFAIK that's not what I need.

4
  • could you please share what you've found and why that doesn't fit for you? Indeed you need to use a pivot chart, but at first create new columns for month and day, then you can filter for column (only September) and put days to x axis. Jun 6, 2016 at 14:11
  • Ok. Well, forgive me if I don't understand the term correctly but I do not see why I need to pivot the table. I can already get the graph to show all values sorted on day based on the day column, the only thing I'm missing is a mean. I have found: stackoverflow.com/questions/7692017/… , quora.com/… , support.office.com/en-us/article/…
    – Jeffrey
    Jun 7, 2016 at 6:46
  • yes, exactly pivot can calculate the average for you. Jun 7, 2016 at 6:48
  • Hmmmm....I have pivotted a table before and it didn't do the same as what's it's doing now. I'm gonna play with it.
    – Jeffrey
    Jun 7, 2016 at 7:04

1 Answer 1

0

So I have reached my goal. Though I might explain how. Since the Office version I'm working with is Dutch, the translation might not be literal so bear with me. Any correction on the correct English terms are appreciated.

Recap: I have weather data for 15 consecutive years of the month september. I want to have a graph showing the following:

  • On the X axis I want all days of september, 1-30.
  • On the Y axis I want the mean for that day, a point/line for every variable (in case of my example 3, temperature, windspeed and hours of sun).

The solution was, after all, a pivot chart. So, create a pivot chart, configured as follows:

  • Columns: values
  • Values: all variables that you want to see in the chart
  • Rows: the day of the month (in my case 1 through 30, that I calculated from the date given by the source
  • As an optional filter I have used weather station as I have data from 2 weather stations

And then, finally for each variable:

  • Click the variable under "values".
  • Click the last menu-option, called something like "Variable field configuration"
  • Click "mean" instead of the standard "sum"
  • Optional: I chose to round up every number to 1 decimal. Click "number notation" and configure the way you want to see each number.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .