Minimum If with dates

[Pumperkin]

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]

=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,""))[>

 

[Special-K99]

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]

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]

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

 

[Pumperkin]

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:

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

Any ideas?

 

[Pumperkin]

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]

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

 

[DanteAmor]

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>