How to find the minimum and maximum in a certain d

Question

enter image description hereI want to get the minimum value in the second column for a certain range that falls within a specific date range. This is all in excel.

For Instance:
Column1: 1/1/2019 1/10/2109 1/15/2019 2/15/2019
Column2: 50 55 60 70

Row 1 would be 1/1/2019 and 50. Row 1, Column 1 would be 1/1/2019.

If some date in Column1 is between 1/05/2019 - 2/30/2019, then give the minimum number in Column2 for that specific date range (1/5/2019 - 2/30/2019).

I don't know if a macro would help or if I could just type a formula in excel. Thanks for your help.

Answer 1

try this. I tested it, and it works. Let me explain the set up since you didn't provide an example of your format. There are two sheets in this macro, sheet 1 contains the dates in column 1, and the values in column 2. Please note, you must put the dates in ascending order, meaning row1 has the oldest date, then follows the next closest date on the calender, and at the very bottom should be the latest date.

Sheet2 contains the different dates you want to get the values from sheet1. I assumed that the first date you want to search for starts on Row1 column1 on Sheet2, and you can search for as many dates as you want.

The logic is, starting from the first cell in sheet2, for every cell in sheet2 column 1, compare the date to sheet1 starting row1 column 1, which is the oldest, if the value is lower, then the macro extracts the value, if it is not lower, then it searches the next cell in sheet1, etc... hope this helps

Sub findMinValue()

Dim lastrow1 As Long, lastrow2 As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim i As Integer, ii As Integer

Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")

lastrow1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
lastrow2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row

ws2.Select

For ii = 1 To lastrow2
    For i = 1 To lastrow1
        If ws2.Cells(ii, 1) < ws1.Cells(i, 1) Then
        ws2.Cells(ii, 2).Value = ws1.Cells(i, 2).Value
        Exit For
        Else 'do nothing
        End If
    Next i
Next ii

End Sub

Answer 2

For example you could use an array formula like so (with the answer linked in comments here in mind (credits to @ScottCraner):

The formula in cell E4 looks like:

=MIN(IF((A1:A4>=E1)*(A1:A4<=E2),B1:B4))

Entered as array through confirming the formula with CtrlShiftEnter

Being put into aggregat, it's still an array formula but you don't have to confirm it as such:

=AGGREGAAT(15,6,B1:B4/((A1:A4>=E1)*(A1:A4<=E2)),1)