Minimum If with dates

Pumperkin

Board Regular
Joined
Jan 24, 2019
Messages
57
Hi, I have a list of weekly dates in Column A and Costs in Column C. I am trying to find the lowest value of C if the date is before this week (as the values constantly go up, using min gives a misleading result as every week starts at £0.

My formula (array) is as follows:

<k11,c:c,""))[ code]<k11,c:c,""))}[="" code]
Code:
=MIN(IF(A:A < K11,C:C,""))
Where K11 is a date 5 days ago... this ensures that the data it pulls is from the correct set of A:A.

However, this keeps giving a value of 0 - and the VLOOKUP for the value shows this week rather than any previous week. If I change the sign to > it works (showing next week as the first zero), but can't get it to work for past dates.

Any ideas anybody?

Thanks in advance.</k11,c:c,""))[>
 
Last edited:

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
Thats an array formula though you make no mention of entering it that way.

With the formula already entered
Press F2 like you're going to edit it then press Ctrl-Shift-Enter

If this doesn't work post some sample data with expected output

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html
 

Pumperkin

Board Regular
Joined
Jan 24, 2019
Messages
57
Thats an array formula though you make no mention of entering it that way.

With the formula already entered
Press F2 like you're going to edit it then press Ctrl-Shift-Enter

If this doesn't work post some sample data with expected output

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html
Hi, Thanks for the reply. I did enter it via Ctrl-Shift-Enter and I have the curly brackets popping up once entered :)

Data is like this:

A B C
28/10/2019 19 £3350
04/11/2019 21 £5000
11/11/2019 23 £2100

So when I enter formula, I want it to pick the figure £3350 rather than £2100 (and the VLOOKUP would show that this is in 28/10/2019). I have the date repeated in H so I can VLOOKUP to date as well :)
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
Try

=INDEX(C1:C3,MATCH(MIN(IF(A1:A1< TODAY(),A1:A3)),A1:A3,1))
Array formula, use Ctrl-Shift-Enter

Assumes all dates will be unique in column A, or you may get incorrect answers.
Replace TODAY() with whatever date you want to use
 
Last edited:

Pumperkin

Board Regular
Joined
Jan 24, 2019
Messages
57
Hi, Thanks for the reply.

I tried but it seems to be giving the value of C for the lowest value of A, rather than the lowest value of C that is before 07/11/2019.

I had to tweak the formula a little from the example as my data set runs to many lines:

Code:
=INDEX(C1:C100,MATCH(MIN(IF(A1:A100 < K11,A1:A100)),A1:A100,1))
Any ideas?
 

Pumperkin

Board Regular
Joined
Jan 24, 2019
Messages
57
Just to add - if I array it, it gives an #N/A. If I just enter, I get the first value of C. Hmmm....
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
Maybe this ?

=INDEX(C1:C1000,MATCH(MIN(IF(A1:A1000<>0,IF(A1:A1000< D1,A1:A1000))),A1:A1000,0),1)
Array formula, use Ctrl-Shift-Enter
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,678
Office Version
2007
Platform
Windows
The formula works well.


Do you really have numerical values ​​in column C?


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >K</td><td >L</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">28/10/2019</td><td style="text-align:right; ">19</td><td style="text-align:right; ">3350</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">04/11/2019</td><td style="text-align:right; ">21</td><td style="text-align:right; ">5000</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">11/11/2019</td><td style="text-align:right; ">23</td><td style="text-align:right; ">2100</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">10-nov</td><td style="text-align:right; ">3350</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >L11</td><td >{=MIN(IF(A2:A4<K11,C2:C4))}</td></tr></table></td></tr></table>
 

Watch MrExcel Video

Forum statistics

Threads
1,098,871
Messages
5,465,192
Members
406,416
Latest member
Revolution_72

This Week's Hot Topics

Top