Minimum If with dates

Pumperkin

New Member
Joined
Jan 24, 2019
Messages
42
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:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
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

New Member
Joined
Jan 24, 2019
Messages
42
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,327
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

New Member
Joined
Jan 24, 2019
Messages
42
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

New Member
Joined
Jan 24, 2019
Messages
42
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,327
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
8,900
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>
 

Forum statistics

Threads
1,082,367
Messages
5,365,028
Members
400,819
Latest member
Gossow

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top