Forum Discussion
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 contai...
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.
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?
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])))
