Search for max in data related to a date

%3CLINGO-SUB%20id%3D%22lingo-sub-1822515%22%20slang%3D%22en-US%22%3ESearch%20for%20max%20in%20data%20related%20to%20a%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1822515%22%20slang%3D%22en-US%22%3E%3CP%3EWe're%20looking%20for%20a%20formula%20or%20method%20to%20search%20the%20maximum%20in%20a%20time%20range%20in%20a%20table%20which%20contains%202%20coloms.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20colom%20contains%20dates%20(no%20relation%20between%20dates%2C%20random)%3C%2FP%3E%3CP%3ESecond%20colom%20contains%20numbers%20(mesures)%20related%20to%20the%20specific%20date%20of%20colom%201.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20want%20to%20search%20for%20each%20row%20(date%20and%20mesure)%20the%20maximum%20in%20all%20the%20mesures%20in%20the%20periode%20a%20year%20before%20the%20date%20and%204%20months%20after%20the%20date.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1822515%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1822701%22%20slang%3D%22en-US%22%3ERe%3A%20Search%20for%20max%20in%20data%20related%20to%20a%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1822701%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F847310%22%20target%3D%22_blank%22%3E%40Danny67%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20C2%3A%3C%2FP%3E%0A%3CP%3E%3DMAXIFS(%5BMesure%5D%2C%5BDate%5D%2C%22%26gt%3B%3D%22%26amp%3BEDATE(%5B%40Date%5D%2C-12)%2C%5BDate%5D%2C%22%26lt%3B%3D%22%26amp%3B%5B%40Date%5D)%3C%2FP%3E%0A%3CP%3EIn%20D2%3A%3C%2FP%3E%0A%3CP%3E%3DMAXIFS(%5BMesure%5D%2C%5BDate%5D%2C%22%26gt%3B%3D%22%26amp%3B%5B%40Date%5D%2C%5BDate%5D%2C%22%26lt%3B%3D%22%26amp%3BEDATE(%5B%40Date%5D%2C4))%3C%2FP%3E%0A%3CP%3EIn%20E2%3A%3C%2FP%3E%0A%3CP%3E%3DMAXIFS(%5BMesure%5D%2C%5BDate%5D%2C%22%26gt%3B%3D%22%26amp%3B%5B%40Date%5D%2C%5BDate%5D%2C%22%26lt%3B%3D%22%26amp%3BEDATE(%5B%40Date%5D%2C12))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1823341%22%20slang%3D%22en-US%22%3ERe%3A%20Search%20for%20max%20in%20data%20related%20to%20a%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1823341%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHey%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20do%20that%20I%20become%20a%20zero%20in%20every%20cell.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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
Highlighted

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. 

Highlighted

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

Highlighted

@PReagan 

 

Hey,

 

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

Highlighted

@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?

Highlighted

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