+ Reply to Thread
Results 1 to 10 of 10

Dealing with a long-term measurement data

  1. #1
    Registered User
    Join Date
    08-01-2013
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    5

    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.

  2. #2
    Registered User
    Join Date
    07-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Dealing with a long-term measurement data

    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?

  3. #3
    Registered User
    Join Date
    08-01-2013
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Dealing with a long-term measurement data

    Hello, Mike. I have attached the file with the raw data (this is how it looks once it is downloaded directly from a database). This is the data of the measurements at one hydrological station located in a specific basin. Once the average yearly values (for each year) of discharge (in mm) for one case are calculated, the data representing measurements in another part of the basin will be processed. There is no mixture of data in the tables that are downloaded from the scientific databases.

    The time is the concern because these are the intermediate calculations used to prepare the data for the other calculations which happen to be alot easier. On the whole, this is a part of the project work/case study.

    Thank you for any help.
    Attached Files Attached Files
    Last edited by Excelmania013; 08-01-2013 at 01:47 PM.

  4. #4
    Registered User
    Join Date
    08-01-2013
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Dealing with a long-term measurement data

    Anyone? Please, help.

  5. #5
    Registered User
    Join Date
    07-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Dealing with a long-term measurement data

    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?

  6. #6
    Registered User
    Join Date
    08-01-2013
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Dealing with a long-term measurement data

    Quote Originally Posted by mike65535 View Post
    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.

    Quote Originally Posted by mike65535 View Post
    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.

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,407

    Re: Dealing with a long-term measurement data

    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.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  8. #8
    Registered User
    Join Date
    07-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Dealing with a long-term measurement data

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

  9. #9
    Registered User
    Join Date
    07-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Dealing with a long-term measurement data

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

  10. #10
    Registered User
    Join Date
    08-01-2013
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Dealing with a long-term measurement data

    Quote Originally Posted by oeldere View Post
    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).

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Long term lurker!!
    By Craig.Selby in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 07-10-2012, 09:37 PM
  2. Dealing with long formulas in Excel 2007
    By thomasutley in forum Excel General
    Replies: 6
    Last Post: 03-02-2012, 09:43 PM
  3. Replies: 1
    Last Post: 07-19-2010, 01:12 PM
  4. [SOLVED] [SOLVED] Long term: Add Symbolic Computation and new math Plot Functions
    By Robert Bigdowski in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-17-2006, 04:25 AM
  5. dealing with very long numbers
    By 149047 in forum Excel General
    Replies: 3
    Last Post: 07-07-2005, 09:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1