Skip to main content
B
BjornM_Sweden4-Participant
4-Participant
September 18, 2023
Solved

Extracting yearly maxima

  • September 18, 2023
  • 2 replies
  • 3624 views

I have 28 years of hourly measured data and I need to extract the maximum value of each 12 month period from the 1st of July to the last of June. The data is in an Excel file. The first column contains the data in the form of YMD e.g. 2023-09-18. The second column contains the hour e.g. 01:00:00. The third column contains the measured parameter e.g. 18.9.

 

Is there an "easy" way to extract the maximum of each 12 month period, using Mathcad Prime? I have the latest version.

 

Kind regards

Björn

 

Best answer by Fred_Kohlhepp

Might actually be easier in EXCEL.  

 

If you have hourly data, then a year is 24 x 365 = 8760 entries except for the seven leap years that would have 8784.

You can divide the data set into 28 vectors (one for each year) then take the maximum of each vector.  (You can also have minimum, average, and standard deviation . . .)

 

Note that if  you format the date column in EXCEL from "date" to "number" before you move it into Mathcad it will be more tractable.

2 replies

W
Werner_E25-Diamond I
25-Diamond I
September 18, 2023

Can you provide a sample Excel file (thinned out heavily, of course 😉 so we can see the exact format, headers, etc.

You may try to read in the Excel file using READEXCEL() and then loop through the data, resetting the max so far if July pops up again.

I guess date and time (time seems to be irrelevant anyway) end up as a string in Prime, right?

    B
    BjornM_Sweden4-ParticipantAuthor
    4-Participant
    September 19, 2023

    Thank you! I will provid an Excel file. Maybeit is still better to use Excel which Fred below suggest. I will try that.

    F
    Fred_Kohlhepp23-Emerald IAnswer
    23-Emerald I
    September 18, 2023

    Might actually be easier in EXCEL.  

     

    If you have hourly data, then a year is 24 x 365 = 8760 entries except for the seven leap years that would have 8784.

    You can divide the data set into 28 vectors (one for each year) then take the maximum of each vector.  (You can also have minimum, average, and standard deviation . . .)

     

    Note that if  you format the date column in EXCEL from "date" to "number" before you move it into Mathcad it will be more tractable.

      B
      BjornM_Sweden4-ParticipantAuthor
      4-Participant
      September 19, 2023

      Thank you! That seems to be a good idea. I will try that.

      T
      21-Topaz II
      terryhendicott21-Topaz II
      21-Topaz II
      September 19, 2023

      Hi,

      I have not altered the Excel file.  Reading it with READEXCEL() in Mathcad gets the Excel day number days since 1/1/1900 not a text date and the time as a fraction of the day multiply fraction by 24 to get hours etc

      Capture.JPG

      What is missing is a way of reading the time stamp in day number and fractions of a day and returning the text date and the time.

      Converting an old C++ Georgian calendar date functions to Mathcad then to Prime this is no longer a problem.

      You just have to change the day number from Excel to Georgian which is just an addition.

      Capture2.JPG

      Cheers

      Terry

        Terms & ConditionsAccessibility statement