Calculation of weather data help

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


0
simon3766 (6)
12/15/2006 10:09:29 AM
excel 39879 articles. 2 followers. Follow

2 Replies
731 Views

Similar Articles

[PageSpeed] 47

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

0
simon3766 (6)
12/15/2006 1:30:29 PM
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
> 
> 
> 
0
mikebres (18)
12/15/2006 10:49:01 PM
Reply:

Similar Artilces:

Procedure to calculate distance using latitude/longitude
Does anyone know of a vba procedure for access that will calculate straightline distance using latitude and logitude for 2 points? AJ wrote: > Does anyone know of a vba procedure for access that will calculate > straightline distance using latitude and logitude for 2 points? See: http://groups.google.com/group/microsoft.public.access/msg/6b934b0e580b974b James A. Fortune MPAPoster@FortuneJames.com ...

Help on Formula
Hi all, Newbie here! I think the post goes here....!?! I have a spread sheet that I cumulate rows G7:G16 (dollar amounts) wit total at G21. Now in H7 to H16 is where I put a "P" or empty t indicate if the corresponding G cell its payed for or not. I would like a formula that would only calculate the rows (G7 thru G1 that have a P in the corresponding H column cell. I'm sure this is possible but don't know how to acheive this. Ca someone help me? Thanks for your help -- Message posted from http://www.ExcelForum.com Instead of creating one simple formula, I create a new...

Data Error
Hi, I have just upgraded from 1.2 to 3.0. I am able to open CRM via IE and get all the tool bars and options however in the data pain I am just recieving the error message: An error has occurred. For more information, contact your system administrator. I have been working on the assumption that there is a permissions error but all seems fine both within CRM and on the SQL side. Any advice ? Thanks Brian ...

Data validation, cell protection or other method?
I have used data validation to set parameters for valid data entry and I have also used cell protection to prevent any type of data entry. What I am trying to do in this instance is to prevent data entry but, instead of the standard message that pops up when using cell protection, I want to display a customized message such as: Data for these fields must be entered in "Project Assumptions" Can Data validation be used to prevent data entry of any kind or do I need to look for a VBA solution? Say you want A1:A10 to be restricted. Select the range, A1:A10, then in Data Valida...

Help with average formula
Hi, I'm trying to calculate average of figures as follows: 1.1.2003 10 2.1.2003 20 3.1.2003 15 4.1.2003 5.1.2003 5 So the question is that if there is one row that has no numbers in it how do I exclude it from the average... meaning that average would b counted like this (10+20+15+5)/4 and not (10+20+15+5)/5. Thanks //juh -- Message posted from http://www.ExcelForum.com Juha If a cell is blank, it won't be included in the average. Andy. "Juha" <Juha.ybp1y@excelforum-nospam.com> wrote in message news:Juha.ybp1y@excelforum-nospam.com... > Hi, > > I...

Urgent!Need help with creating a Christmas PowerPoint slideshow
I'm creating a Christmas PowerPoint slide show of my family,can someone tell me where I can download free Christmas templates for my presentation? And I want to put this PowerPoint slideshow on Youtube to share with all my relatives and friends. I tried to upload an.PPT file to Youtube but failed, any idea? Thanks Dwight, There are some templates on office online: http://office.microsoft.com/en-us/templates/results.aspx?qu=CHRISTMAS&sc=4&av=ZPP -- Luc Sanders MVP - PowerPoint "Dwight" <Dwight@discussions.microsoft.com> schreef in bericht new...

Calculating Taxes and Making calculations
Hello, Kindly plz tell me how do i go about making some small calculations based on my existing data such as how to compute taxes and do other calculations based on some quantitative data. where exactly do i make modifications on my application??? Kindly help me out. Thanks , swati you can add code in javascript behind dropdowns or piclist fields. One example might be a dropdown which has shipping options ie ups, fedex etc. When you choose one it takes the weight of your package (whcih you enter) then calculates a shipping cost. search the archives of this group at groups.google.com and ...

Reformat data to vertical format
Here is what I am trying to do. http://www.totalcontrolproducts.com/totalcontrolproducts_OLD/download/images/Untitled-1.gif I have about a thousand records that I need in a vertical format with normal shared field name. Any suggestions -- Psydwaz ----------------------------------------------------------------------- Psydwaze's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2437 View this thread: http://www.excelforum.com/showthread.php?threadid=37965 see Creating a Spreadsheet from Database data (#dbdata) http://www.mvps.org/dmcritchie/excel/snakecol.htm...

Move data from one server to another
We are trying to move some databases from a set of old servers to a new servers and they are pretty large.. like around 100 databases and around 10-15 TB. They are all SAN connected and I believe SAN based replication from one SAN volume to a new SAN volume connected to the other server might be faster.. but I don't think SAN based replication works at a file level.. As an example, I may have 4 databases on one server and 2 of these databases may move to NewServer1 and the other 2 to NewServer2... Backup and restore may take forever.. Any other way to speed up these moves...

Interactive Chart Data?
Is it possible to select a group of data on a graph/cross-plot and then "zoom" to or "highlight" that data in the spreadsheet? I hope it's possible and I am just not smart enough to figure it out. Thanks for your time. Justin Hi Justin- I believe what you are asking would be somewhat complicated to do and require writing code. You might try one of these options instead: 1- If your chart is on a separate Chart Sheet, rt-click the white space (Chart Area) and choose Chart Options. On the Data Table tab put a check in the box for Show Data Table to have a copy o...

Install Module choices mapped to Help Menu Module Listing
Hello, when installing GPv10 you have choices of what to install. For example: EFT for Receivables, Electronic Bank Reconciliation, Professional Services Tools Library, etc. But when you go under Help>Options and display the registration report it's not a one-to-one mapping of what you chose when doing the install. For example modules listed in my registration report that are not registered are Cashbook Bank Management, Bank One Commercial Card Integration, Bank One Direct Paycheck Card Integration. I don't know if these are mapped to EFT for Receivables or Electronic Bank Recon...

entering data in excel invoices
-- TomA --- PattiD .. >-----Original Message----- > >-- >TomA >. > ...

How to calculate age of a person?
Hi, I'm struggling to figure this one out. I have a date column and I'd like to be able to calculate the age without doing it in my head. I've tried to do this but am struggling. Any help will greatly appreciated. Thanks Suzanne Visit www.cpearson.com and search for DATEDIFF best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Grd" <Grd@discussions.microsoft.com> wrote in message news:6BFFD11C-AECB-4837-93B6-D84B6170B975@microsoft.com... > Hi, > > I'm struggling to figure this one out. I have...

Help with Office Outlook
I recently changed from Windows Mail to Office Outlook 2007 on my Vista Home premium System. Everything is working as I believe it should. There is one thing however, which I find time-consuming and annoying but perhaps I am not using the right sequence. With Windows Mail, I can open an incoming message and, when I finish reading it, I can go on to the next message (already opened) just by pressing a "down arrow" on the tool bar. There is no such arrow in Outlook 2007 and to read the next message, I have to go back to the message list and start over again to select a...

EXCEL 2007 Formula to calculate INTEREST only on a 3 month bridge
I am trying to calculate monthly INTEREST ONLY payment on a short term bridge/swing loan. Assuming an interest rate of 2.75%, paid monthly, Also assuming it will be required for a 3-6 month period, amount approx $500,000. Just switched to Excel 2007 but don't seem to be able to calculate using the formula builder. Not sure if it is compounded daily or monthly. You might want to check out the IPMT function. From the XL help file: IPMT(rate,per,nper,pv,fv,type) Rate is the interest rate per period. Per is the period for which you want to find the interest and must...

HELP, How do I share Outlook folders on a local network WITHOUT Exchange server??
If you're talking about Personal Folders in Outlook (PST files), the best thing I can suggest is to locate the folder where the PST file is stored on the host machine, share that folder, and map to that folder from the other PC. Then add the remotely shared PST file to the Outlook profle (accessing the shared file on your network) .. >-----Original Message----- > >. > Unfortunately, Outlook requires exclusive access to PST files, so only one person can have Outlook open to access that file at any time. A good place to start looking for other solutions would be: http:/...

Help 02-23-08
I have a form to input information into two different tables. I want to click on a button and have a field called NC# populated with the autonumber from table1. The tables are set up like this. Table1: Table2: CAR_ID-Auto Number NCID=Auto Number CAR#=text CARID=Text Information=text Information=Text So now when you click on the buttin it will look at CAR_ID in table1 and input this into CARID in table2. On Sat, 23 Feb 2...

Due Date Calculated
I need assistance on an expression that allows for the due date of recurring training to be one year from date completed. Details are as follows: I have a form named [frmTRNComplete] based on table named [tblTRNComplete] Form controls are: TRNID (combo box), DateComp (Text Box) , DateNext (text box), Reccuring (check box) I would like the DateNext to be 1 year from DateComp if Reccuring is true and Date next to be blank if Reccuring is false. -- Aloha, Ron A. Ron I'll assume that you want to use the form to calculate the DateNext, and that you are not trying to s...

Publisher and PDF help...
I am trying to find a solution to my delima. I have publisher on one computer and adobe acrobat on another. Is there a Publisher viewer or some work around that anybody knows of. Thanks in advance... While in a state of ecstasy after repairing his laptop, Ed sees a message from Neal <Neal@discussions.microsoft.com>. On it is written: > I am trying to find a solution to my delima. I have publisher on one > computer and adobe acrobat on another. Is there a Publisher viewer or > some work around that anybody knows of. Options: a) Output a PostScript file from Publisher and di...

how to calculate outliers
Google is your friend: http://www.google.com/search?q=calculate+outliers - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "ismhs" <ismhs@discussions.microsoft.com> wrote in message news:035124A0-06FA-4153-932E-6C4E0614F38C@microsoft.com... > ...

Filter data into separate worksheet
I'm in the process of trying to make a spreadsheet for work but I'm just clueless... excel is not my area of expertise! :) The original spreadsheet has 10 columns with various pieces of information... column D is what is important to me... column D has 5 choices for "reasons"... I want the main tab to remain untouched but in the other 5 tabs of the spreadsheet, I want the different "choices" to then filter into the other tabs... For example - the choices include "Coverage" "Equipment" "Customer Service", etc... I want the tab entitled...

Help setting up Charts
I have a spreadsheet with the following Column F, building name Each row on the spreadsheet is a member of staffs name In Column I, I have got the number 1 if the member of staff has been trained. If not there is a 0. I need a Chart to show the number of members of staff who are trained per work area. In the chart/ graph the work area should be along the bottom row X. The number of staff members will be in the Y column. Please would you be able to give some assistance. When I try to create a chart the information does not show and it appears to be cluttered. There are 91 members of s...

help please #9
sorry for the double post I am having a hard time trying to figure out how to do something and was wondering if anyone could point me in the right direction. I need to migrate user mailboxes from one server to another, easy enough right... well here is where my issues are. server one is in say abc.local domain server two is in say xyz.local domain there are the same users in both domains, with the same naming convention. so to clear that up if i have a user called ttest in domain abc, user ttest is also in xyz. the old administration had seperated these domains due to function and sec...

Calculated field options???
Here's my Data: Country Status --------- ---------- Canada Green Canada Blue Canada Green Canada Green Canada Blue In my pivot table, 'Country' is an item, and 'Status' is my only data field. What I really need to show in my data area is two column of data, one for 'Green' and one for 'Blue'. I tried creating two 'Calculated' fields with the following formulas, but they don't seem to be working: 'CalculatedField1' = IF((STATUS="Green"),1,0) 'CalculatedF...

tenure calculations
how can I format several dates such as 04/19/2003, 04/01/2004, etc. int a yearly average for the employee tenure -- Message posted from http://www.ExcelForum.com right now say I have my yearly figures in i1-i9. The formula I though would work is =today()-average(i1:i9), but how would i format this to give me yearly read out -- Message posted from http://www.ExcelForum.com Hi djackson! One way is to use YEARFRAC with a third argument of 1 to get your tenures in years and fractions of a year and then average the result. However YEARFRAC does produce some annoying but small errors. Y...