New Contributor

# 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.

5 Replies

# Re: Search for max in data related to a date

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.

# Re: Search for max in data related to a date

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))

# Re: Search for max in data related to a date

Hey,

If I do that I become a zero in every cell.

# Re: Search for max in data related to a date

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?

# Re: Search for max in data related to a date

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])))`