Rolling Min Max of Last 12 Months

[csimarketing]

Hello,

I am trying to get a rolling/dynamic formula that calculates the MIN and MAX of the most recent 12 months, but all of the threads I can find are not specific enough. Any help would be greatly appreciated!

MIN formula should return: 2
MAX formula should return: 84

Month	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20	Jan-21	Feb-21	Mar-21	MIN Last 12 Months	MAX Last 12 Months
QTY	20	4	54	16	77	84	2	46	65	43	12	34	54	78	13

Thank you!

 

[steve the fish]

Try:

=MINIFS($B$2:$P$2,$B$1:$P$1,">="&EOMONTH(TODAY(),-12)+1,$B$1:$P$1,"<"&EOMONTH(TODAY(),0)+1)

Same for max but MAXIFS.

 

[csimarketing]

=MINIFS($B$2:$P$2,$B$1:$P$1,">="&EOMONTH(TODAY(),-12

Hi Steve, the MAXIF formula works great!

However, the MINIF formula is still including Zeros as a min. Is there a way I can exclude Zero values or only values greater than Zero to be counted towards the MIN?

I should have mentioned that the data above is just a sample set, my actual data set does have some months with Zero as their value.

 

[steve the fish]

Sure just add that in:

=MINIFS($B$2:$P$2,$B$1:$P$1,">="&EOMONTH(TODAY(),-12)+1,$B$1:$P$1,"<"&EOMONTH(TODAY(),0)+1,$B$2:$P$2,">0")

 

[csimarketing]

These worked great, Thank you! You saved me hours of trial and error!