Lookup min max values within a date range
This article explains how to find the smallest and largest value using two conditions. In this case they are date conditions but they can be whatever you like, displayed in cell C3 and E3.
The maximum value in that date range is calculated in cell C6, The minimum value in cell C7. There is also a formula that finds these values and return their corresponding date, in cell E6 and E7.
Formula in cell C6:
The MAXIFS function returns the largest number from max_range ($C$10:$C$39) based on a condition or criteria.
MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
The first condition $B$10:$B$39, "<="&$E$3 identifies rows whose date is smaller than or equal to date in cell E3.
The second condition $B$10:$B$39, ">="&$C$3 identifies rows whose date is larger than or equal to date in cell C3.
Formula in cell C7:
The MINIFS function works exactly the same as the MAXIFS function, however, the smallest number is instead returned.
Formula in cell E6:
This formula is a simple lookup formula, it returns only a single value from column B if the value in column C matches the contents of cell C6.
INDEX($B$10:$B$39,MATCH(C6,$C$10:$C$39,0))
The MATCH function returns the relative position of a value in a cell range or array.
INDEX($B$10:$B$39,MATCH(1030.98,$C$10:$C$39,0))
becomes
INDEX($B$10:$B$39,5)
The INDEX function returns a value based on a row (and column number if needed).
and returns 8-27-09 in cell E6.
Formula in cell E7:
Get excel *.xlsx file
Lookup min max values in a date range.xlsx
Dates category
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
Excel categories
9 Responses to “Lookup min max values within a date range”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
How would you go about doing the above while excluding any values that happen to be zero?
Thomas
=MIN(IF((Date_col<=$E$3)*(Date_col>=$C$3)*(Close_col<>0), Close_col, ""))
You are a lifesaver! I've been trying to figure this out for hours, until I came across your site.
What does the asterisk do in the formula? I know it's not multiplying anything, but I have never seen it this way.
=MIN(IF((Date_col=$C$3)*(Close_col0), Close_col, ""))
Mark McPherson,
Thank you!
The asterisk multiplies two arrays.
Example, (TRUE, TRUE, FALSE)*(TRUE, FALSE, FALSE) equals (1,0,0).
1 = TRUE
0 = FALSE
If there are repeated values in the close column, the expression returns the first occurrence of the value, regardless of the identified data range. how do we modify the formula to return the value in the date range identified?
its so heplful! thank you so much
Thanks, this one was giving me a headache!
Thank you so much!
I have been doing my head in trying to find a way to do this (it seems like it would be a simple enough request but have still struggled until now). I've adapted your formula slightly to fit my purpose, but basically I had to be looking up max flood levels that occurred within a certain date range of a storm event, in my case: the date +-2 days. Instead of having the date range sitting separately, I nested it into my forumlas. This allows me to see the max height of a flood at multiple different flood gauges across s river system as the storm and flows pass through the system which can take days.
=MAXIFS($F$3:$F$40000,$E$3:$E$40000, "="&(AE34-2))
F = flood height (max values I need)
E = list of dates corresponding to the flood height
AE = specific date (the +- 2 searches 2 days before or after the event).
Thank you again.
Hello,
I need to do exactly this, excpet in the example you have the "close" column, but i have 48 columns to seek the MIN from.
I have 1 day per row data from May 2019 with 48 data points per row to the present day and i wish to extract the MIN.
I had been manualy naming a range then doing =MIN(named_range) but its tedious. Being able to do by date is much better but most solutions are hampered by a need for the range and data range to be the same size and shape.
Any tips?
Many thanks,
Stuart