CODEWITHSUNDEEP
excel-formula
ClockworkNemo
ClockworkNemo

Reputation: 61

Excel - Is there a way to calculate min and max values in a table between a date range?

For the table on the right, I would like to calculate the min and max values in the Plus and Minus columns between two dates in the Month-Year column. I would like to use the Start and End boxes below the table to control the min/max calculations

Is there a way to do this with formulas? I've tried the MINIFS() functions, but I can only get it to work with a single month-year. I'm not sure how to expand it to both a start and end month-year.

enter image description here

Upvotes: 1

Views: 440

Answers (1)

Dattel Klauber
Dattel Klauber

Reputation: 833

The problem here is, that your month-year values are not formatted as numbers but as text. The consequence is, that you cannot use simple operators such as < and > to filter the data for the relevant dates.

There are two solutions that immediately come to mind. The first would be to convert the month-year text values into numbers within the formula. The second would be to just search for a match in the text values.

I believe the latter is the shorter solution so here is how that works:

=MIN(OFFSET($K$2,MATCH($H$43,$J$2:$J$37,0)-1,0,MATCH($I$43,$J$2:$J$37,0)-MATCH($H$43,$J$2:$J$37,0)+1))

That is for finding the minimum in the 'plus' range. Adjust for maximum and 'minus' range accordingly.

Upvotes: 1

Related Questions