Excel Rainfall Formula

[Nicko38]

I am new to Excel & I am using Excel 2000. I am starting to compile a Rainfall Workbook which has a new sheet for each year. It will have 16 sheets initially (1997 – 2012), more of course as required.
I am only laying out the first sheet at this stage with Labels & Formulas – no values yet. Each sheet has Month columns & Day rows. When I have the layout finished on the first sheet, I will copy the first sheet to make all the other sheets.
Each sheet so far has the following Rows under the day 31 Row:- "Total rainfall for each month", "No. of days rain fell each month", "Sum each month together to have rainfall so far & total rainfall for each year". I have managed to work out the formulas to do these.
What I would like now, for the bottom row, is the formula to work out "Average rain for each month" (over the 16 years, so far) & "Average rainfall per year" (over the 16 years, so far)
E.g. Add 1997 January to 1998 January sheet, then get average for those 2 Januaries in bottom row of 1998 sheet & so on until I have the average for the 16 Januaries. Then, in the bottom right, cell, the same for yearly averages.
Can any kind person, please compose this formula for me.
Nicko38 - Australia

 

[yabi]

If you are going to work with all sheets, I recommend you to input all years data in one sheet. Just add another column and label it "year". It will simplify most of your calculation.

If you really persists to input data in different sheets, and you have to have strong reason for that, you can use summation over different sheets, which is possible and named "Group". First you select all requested sheets by clicking on the first sheet label, then keep shift button and click on the last sheet label. This selects many sheets and what ever you select in active sheet, will be selected in all group. You can define a sum on many sheets using Group feature.

 

[Mouse1957]

Hi Nick and Yabi, I am in the process of creating a progressive rainfall chart for the years 2006 to 2014 and onwards. I have very basic Excel (2010) skills. I note Yabi you recommended putting all years' data on one sheet. This I can do....so far I have a very basic s/sheet as follows:

A1 - Label - Year - I'm not sure if I have this in the correct cell. If so 2007 label would go in N1.
B2 - Label - 2006
B2 - Label - Jan, C2 - Feb, D2 - Mar etc....
A2 - Label - Day
A3 - Number 1, A4 Number 2, A5 Number 3 etc....
Row 33 has the last day ie: 31 in A33

Row 34 has Total monthly - I would use the sum function for this total.
Row 35 has Average Monthly - I would use the average function for this total.
Row 36 has No of Rain days - I would like help in creating this formula as I have no idea.
Row 37 has Total Yearly Rainfall - again I would use the sum function for this total
Row 38 has Average Yearly Rainfall - again I would use the average function for this result.
Row 39 has No of rain days over year - I would like help in creating this formula too.

There are three months (May, Aug & Oct) where no rainfall was received. How do I get a zero in place of the ##### signs?

Once the data is entered it would be great to be able to chart this info and I would welcome your recommendation as to what type of chart or charts would be most appropriate.

I hope the above is clear and I look forward to your response.