Min value between 2 dates

[nigelbloomy]

I have tab 1 that lists every day on it. Next to every day is a dollar amount. Tab 2 lists the start date of each week. For each week on tab 2 I want to see what the minimum dollar value is from the values on tab 1.

Tab 1
1/1/2014 $20
1/2/2014 $10
1/3/2014 $10
1/4/2014 $15
1/5/2014 $15
1/6/2014 $20
1/7/2014 $20
1/8/2014 $15
1/9/2014 $15
...


Tab 2
1/1/2014 (Min value of dates 1/1/2014 through 1/7/2014) $10
1/8/2014 (Min value of dates 1/8/2014 through 1/14/2014) $15

I can sum the values between those dates with sumifs(), I can count them with countifs(). But I don't know how to find the minimum value between them.

[AlKey]

Try this in C1 on sheet 2

=MIN(IF(Sheet1!A1:A9>=A1,IF(Sheet1!A1:A9<=B1,Sheet1!B1:B9)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.


on Sheet 2

where A1=1/1/2014
B1=1/8/2014

[nigelbloomy]

It works. Thank you. It takes some serious processing. I've got 3700 days of data in tab 1 and over 500 weeks in tab 2.

I was trying to use index or vlookup to find the first day in the week and then look up the next 6 days as well. That didn't seem to work. Is there something I could change on the first tab with the days to make this process easier?

[AlKey]

Thank you for the feedback!. Of course, if you have to work with a high volume of data you would need to look for a Vb solution.

[sktneer]

Try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of just Enter.
On sheet2, in B2

Please Login or Register  to view this content.

and then copy down.

[nigelbloomy]

I ended up using the following formula:
=IF(A2="","",MIN(INDIRECT("'Sheet 1'!B" & MATCH(A2,'Sheet 1'!A:A,0) & ":B" & (MATCH(A2,'Sheet 1'!A:A,0)+6))))
The match part finds the row on tab 1 that matches the begining of the week. If I add 6 to that result then I have the range where I want to find the minimum value. This seems to run pretty fast even with 3700 rows of days.

[sktneer]

Another way......
Try this regular formula.

Please Login or Register  to view this content.

The problem with this formula and the one which you posted in your last reply is that if you skip a date, it will produce the wrong result. But the choice is yours.