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

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


toni.gee wrote:
> If you only want to look at your data for a paricular month, you could use
> Autofilter to select the month and year you are interested in and then
> summarisie the filtered data.
>
> If you want to see all of the summarised data together then you will
> probably need to use Array Formulas and AVERAGE IF and SUM IF.
>
> If you want to get fancy and be able to display individual months year on
> year as well as month by month, then you probably need to use Pivot Tables.
>
> The three options get progressively more complex, but all are possible from
> the same data that you outlined.
>
> "Simon Keeling" wrote:
>
> > 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
> >
> >
> >

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


"Simon Keeling" wrote:

> 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
>
>
>