Oct 27 2020 05:15 AM
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.
Oct 27 2020 06:06 AM - edited Oct 27 2020 06:07 AM
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.
Oct 27 2020 06:08 AM
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))
Oct 27 2020 08:23 AM
Oct 27 2020 09:15 AM
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?
Oct 27 2020 10:27 AM
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])))