# Rolling Min Max of Last 12 Months

#### csimarketing

##### New Member
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!

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### steve the fish

##### Well-known Member
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

##### New Member
=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

##### Well-known Member

=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

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

Replies
0
Views
45
Replies
8
Views
246
Replies
10
Views
255
Replies
5
Views
180
Replies
2
Views
158

1,130,119
Messages
5,640,218
Members
417,131
Latest member
Seanr19871

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back