Dealing with a long-term measurement data

Hello, everybody. I am new here and would like to ask for your help with the calculations of the long-term averages.

Let's sart with a dscription of my situation..

The data of the long-term (48 years) hydrological measurement data I currently work with consists of the monthly discharge data (every 12 months of 48 years). It is presented in a tabular form (observation post, river, year, month, average monthly discharge in cub. m/s).
Firstly, the units of monthly values of discharge should be convert/recalculate from cub. m/s to mm [Q(monthly) = value * 60 (s) * 60 (min.) * 24 (h) * D (number of days in a given moonth: 28/29, 30 or 31)/A (area of the catchment) * 1000] [values that are fixed in this case are marked with bold]. Secondly, the yearly values (12 month averages) for each year (the period is 1961 to 2009) are to be calculated.
While it is quite easy to recaculate each value (convert to other unit of measurement), the calculation of the year averages seems to be a more time-consuming procedure. I realised that the calculation of the yearly (12 month average) values should follow this algorythm: Average(G3:G14) (the data set starts from the third row), then Average (G15:G26), etc.

Would it be possible to write a formula or a macro code that helped to work (convert the units of data and calculate the averages of each year) with many more similar tables (same data, different values)?

Thank you very much for any help. Hope, we can find a sollution soon.

Could you provide a typical table in XLS form? I' m confused as to the organization of the date - e.g., is the data from various observation posts mixed with other observation posts or is it segregated?

re: time-consuming procedure
Are you concerned with the time necessary for creating the pages and formulas, or the time necessary for the calculations themselves to run?

Anyone? Please, help.

1) I don't understand the values in column E also, there are numbers like 400 * 10^12 and numbers like 900. Nine orders of magnitude difference? Really?
2) Also you have values like 330,1 and 305,25 - what are those?
3) I'm also still confused as to what you want exactly.

All I can gather is you want to calculate some sort of conversion and normalization? In other words, for each row in column F, say, do something like this:

= E3 * 60 * 60 * 24 * D (number of days in a given month: 28/29, 30 or 31)/A (area of the catchment) * 1000]

Is that all you want to calculate?

You need a VLOOKUP for the days in the month. WHere is the area of catchment available?

Originally Posted by mike65535

1) I don't understand the values in column E also, there are numbers like 400 * 10^12 and numbers like 900. Nine orders of magnitude difference? Really?

2) Also you have values like 330,1 and 305,25 - what are those?

The values are the long-term data of hydrological measurements in a given catchment (in cub. m/s). Some of them might be extrapolated (restored) and such values are not rounded. Due to this reason there are many digits after the comma in some cases.

Originally Posted by mike65535

3) I'm also still confused as to what you want exactly.

All I can gather is you want to calculate some sort of conversion and normalization? In other words, for each row in column F, say, do something like this:

= E3 * 60 * 60 * 24 * D (number of days in a given month: 28/29, 30 or 31)/A (area of the catchment) * 1000]

Is that all you want to calculate?

You need a VLOOKUP for the days in the month. WHere is the area of catchment available?

Exactly. Firstly, I would like to recalculate the given values using the other unit of measurement (from cub. m/s to mm). It were really great, if it was possible to modify a formula so that the number of days was changed accordingly to the month/year (28/29, 30 or 31).
Secondly, to calculate the average yearly discharge using the recalculated values. The desired outcome is the values of discharge representing 48 years of measurements (1 year - 1 value, i.e. averages of every 12 months).
What I struggle to do is writing an appropriate VLOOKUP code that helped to deal with this task.

I see one of *my* problems - your data uses the EU convention of commas where I'd use decimal points. I suspected that , but I didn't realize one also then used multiple commas to wrap groups of three. (In the US we use one decimal between the one's column and the tenths column. As the kids say, "My bad!"

Here's a simple example of a VLOOKUP

Sheet two holds the look up table

On sheet one, enter a month number into column A and column B

If you want to handle leap years for Feb, you'll need to add some more sophistication

Originally Posted by oeldere

Not totaly sure about what you want to achieve,

But reading your question, I think the solution would (could) be given by an pivot table.

See the attached file.

Please reply back, for futher (better) solutions.

Hello, Oeldere. The table looks very good. However, each value in it should be expressed in other units (not in cub. m/s as given, but in mm). The formula used for the unit conversion is given in the previous posts. It were reakky great, if it was possible to easily convert the units and then express the values in such form (a pivot table).