Calculation of weather data help

[Simon Keeling]

Hi,

I have a data file (at http://www.weatherweb.net/cliall.dat) which contains
daily weather data from 1881 in the format:
dd,mm,yy,max temp,min temp, rainfall, sunshine, wind speed, wind gust,
pressure

What I would like to do is to calculate means for max, min, wind speed, wind
gust and pressure, and also total rainfall and sunshine for each month.

I would like the output in the format:
mm,yy,mean meax, mean min, total rain, total sunshine, mean wind, mean wind
gust, mean pressure

Can anyone give any assistance as to how I might do this in excel to create
a sngle file contain all the monthly weather data?

Thanks in advance,
Simon Keeling
Wombourne, UK

 

[Simon Keeling]

Thanks Toni,

I must admit, I think this is way above my capabilities. I have had a
go at a Pivot Table but can only get it to calculate the first column
(day). There has to be a way of doing this without me resorting to pen
and paper!

Thanks again,
Simon

 

[Guest]

I would use Array Formulas. Not that Pivot Tables are bad, I'm just more
familiar with Array Formulas.

Here is the process I go through any time I want to summarize a list:
I name the ranges that I want to work with. In your case the Month column
and each of the columns you want to summarize. If you're not familiar with
doing this, put your cursor in the first cell of the range you want to name.
Then press CTRL SHIFT down arrow. Assuming it's a continuous range, this
will highlight the range. Then in the small box at the top left of the
screen, where you normally see the cell reference, enter a name, press enter.

Naming the range's isn't necessary, but it makes the formulas easier to
understand. You can always just put in the range reference such as
cliall!C2:C45768.

Then add a worksheet, and on this new sheet make a list of the comparison
values. In this case numbers 1 through 12 for the months. Let's assume the
months are in cells A3:A14
Label your cells across the top for each category, i.e. Max, Min, etc.
Then in cell B3 enter this formula =AVERAGE(IF(vMonth=$A3,MaxTemp,""))
While you are still in the forumla editor line press CTRL SHIFT ENTER.
After you do that your formula will look like this
{=AVERAGE(IF(vMonth=$A3,MaxTemp,""))}. Excel will add the curly brackets.
Copy this cell down for each of the months in your list. You will end up
with this:
Month Max
1 {=AVERAGE(IF(vMonth=$A3,MaxTemp,""))}
2 {=AVERAGE(IF(vMonth=$A4,MaxTemp,""))}
3 {=AVERAGE(IF(vMonth=$A5,MaxTemp,""))}
4 {=AVERAGE(IF(vMonth=$A6,MaxTemp,""))}
5 {=AVERAGE(IF(vMonth=$A7,MaxTemp,""))}
6 {=AVERAGE(IF(vMonth=$A8,MaxTemp,""))}
7 {=AVERAGE(IF(vMonth=$A9,MaxTemp,""))}
8 {=AVERAGE(IF(vMonth=$A10,MaxTemp,""))}
9 {=AVERAGE(IF(vMonth=$A11,MaxTemp,""))}
10 {=AVERAGE(IF(vMonth=$A12,MaxTemp,""))}
11 {=AVERAGE(IF(vMonth=$A13,MaxTemp,""))}
12 {=AVERAGE(IF(vMonth=$A14,MaxTemp,""))}

Which gives these results:
Month Max
1 5.8
2 6.4
3 8.9
4 11.9
5 15.6
6 18.6
7 20.3
8 19.9
9 17.2
10 13.0
11 8.8
12 6.4

Hmmm, I'm guessing either this is a very cold place or these are celsius.
Anyway, do this for each of your columns changing the named range for each
one.

What's nice about using array formulas is that you can add several
conditions (IF clauses) to the formula. For example you could average the
values for every day of the week in each month. Just create another column
and convert the date to an actual date value. convert it using the WEEKDAY
function then change the formula to check for the week day also:
{=AVERAGE(if(wDay=1,IF(vMonth=$A14,MaxTemp,"")))}
or make another list across the top with 1 to 7 and reference these in your
formula"
{=AVERAGE(if(wDay=C$2,IF(vMonth=$A14,MaxTemp,"")))}

Don't overlook array formulas either, they are a great way to summarize data.

Good Luck
Mike