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
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 […]
The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]
Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]
The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]
Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]
This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains the […]
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
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