Forum Discussion

Danny67's avatar
Danny67
Copper Contributor
Oct 27, 2020

Search for max in data related to a date

We're looking for a formula or method to search the maximum in a time range in a table which contains 2 coloms.

 

First colom contains dates (no relation between dates, random)

Second colom contains numbers (mesures) related to the specific date of colom 1.

 

We want to search for each row (date and mesure) the maximum in all the mesures in the periode a year before the date and 4 months after the date.

excel
Formulas and Functions

5 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 27, 2020

    Danny67 

    In C2:

    =MAXIFS([Mesure],[Date],">="&EDATE([@Date],-12),[Date],"<="&[@Date])

    In D2:

    =MAXIFS([Mesure],[Date],">="&[@Date],[Date],"<="&EDATE([@Date],4))

    In E2:

    =MAXIFS([Mesure],[Date],">="&[@Date],[Date],"<="&EDATE([@Date],12))

  • PReagan's avatar
    PReagan
    Bronze Contributor
    Oct 27, 2020

    Hello Danny67,

     

    For Max last year, that could be:

    =MAXIFS([Mesure],[Date],"<="&[@Date],[Date],">="&DATE(YEAR([@Date])-1,MONTH([@Date]),DAY([@Date])))

    For Max 4 months, that could be: 

    =MAXIFS([Mesure],[Date],">="&[@Date],[Date],"<="&DATE(YEAR([@Date]),MONTH([@Date])+4,DAY([@Date])))

    For Max after 1 year,

     =MAXIFS([Mesure],[Date],">="&[@Date],[Date],"<="&DATE(YEAR([@Date])+1,MONTH([@Date]),DAY([@Date])))

    Please review for correctness. 

    • Danny67's avatar
      Danny67
      Copper Contributor
      Oct 27, 2020

      PReagan 

       

      Have found the problem. Now I would make one change.

       

      If i would calculate this only within the same parcel, and for each parcel seperate. How do i make this supplemantary condition?

      • PReagan's avatar
        PReagan
        Bronze Contributor
        Oct 27, 2020

        Danny67 

         

        Include the criteria [@Parcel] to the criteria_range [Parcel]:

        For Max last year, that could be:

        =MAXIFS([Mesure],[Parcel],[@Parcel],[Date],"<="&[@Date],[Date],">="&DATE(YEAR([@Date])-1,MONTH([@Date]),DAY([@Date])))

        For Max 4 months, that could be: 

        =MAXIFS([Mesure],[Parcel],[@Parcel],[Date],">="&[@Date],[Date],"<="&DATE(YEAR([@Date]),MONTH([@Date])+4,DAY([@Date])))

        For Max after 1 year,

         =MAXIFS([Mesure],[Parcel],[@Parcel],[Date],">="&[@Date],[Date],"<="&DATE(YEAR([@Date])+1,MONTH([@Date]),DAY([@Date])))

         

Resources