Minimum If with dates

Pumperkin

Board Regular
Joined
Jan 24, 2019
Messages
59
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
 
Upvote 0
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 :)
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
Just to add - if I array it, it gives an #N/A. If I just enter, I get the first value of C. Hmmm....
 
Upvote 0
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:
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top