Need formulae to get first and last date

Mathman

Board Regular
Joined
Jan 28, 2017
Messages
152
Office Version
  1. 2016
Platform
  1. Windows
Hi


What I need to get is the first date when a number appears in column M which is 9-Jan-17 (min date) and the second is the last date a number appears in column M which is 3-Feb-17 (max date) Also it must also fall between the dates that are in columns LL and LM.


Any idea how I get arrive to the answer?


Thank you
MM








Column LLColumn LM
DateDate
>=40949<=42776
Column BColumn M
DateT2
10-Feb-17
9-Feb-17
8-Feb-17
7-Feb-17
6-Feb-17
3-Feb-171.69
2-Feb-17
1-Feb-17
31-Jan-171.68
30-Jan-171.68
27-Jan-17
26-Jan-17
25-Jan-17
24-Jan-171.68
23-Jan-171.68
20-Jan-17
19-Jan-171.68
18-Jan-17
17-Jan-171.67
16-Jan-17
13-Jan-17
12-Jan-171.67
11-Jan-17
10-Jan-17
9-Jan-171.67
6-Jan-17
5-Jan-17
4-Jan-17
3-Jan-17

<tbody>
</tbody>
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
If the dates will always be in newest to oldest then this should work


Excel 2010
BCDEFGHIJKLM
1min datemax date
21/12/20172/3/2017
3
4
52/10/2017
62/9/2017
72/8/2017
82/7/2017
92/6/2017
102/5/2017
112/4/2017
122/3/20171.67
132/2/2017
142/1/2017
151/31/20171.67
161/30/20171.67
171/29/2017
181/28/2017
191/27/2017
201/26/2017
211/25/2017
221/24/20171.67
231/23/20171.67
241/22/2017
251/21/2017
261/20/2017
271/19/20171.67
281/18/2017
291/17/20171.67
301/16/2017
311/15/2017
321/14/2017
331/13/2017
341/12/20171.67
351/11/2017
361/10/2017
371/9/2017
381/8/2017
391/7/2017
401/6/2017
411/5/2017
421/4/2017
431/3/2017
Sheet5
Cell Formulas
RangeFormula
D2{=INDEX(B5:B43,LARGE(IF(ISNUMBER(M5:M43),ROW(B5:B43)-ROW(B5)+1),1))}
E2{=INDEX(B5:B43,SMALL(IF(ISNUMBER(M5:M43),ROW(B5:B43)-ROW(B5)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Excel Workbook
BMNOPLLLM
1DateT2First9-Jan-17
210-Feb-17Last3-Feb-1710-Feb-1210-Feb-17
39-Feb-17
48-Feb-17
57-Feb-17
66-Feb-17
73-Feb-171.69
82-Feb-17
91-Feb-17
1031-Jan-171.68
1130-Jan-171.68
1227-Jan-17
1326-Jan-17
1425-Jan-17
1524-Jan-171.68
1623-Jan-171.68
1720-Jan-17
1819-Jan-171.68
1918-Jan-17
2017-Jan-171.67
2116-Jan-17
2213-Jan-17
2312-Jan-171.67
2411-Jan-17
2510-Jan-17
269-Jan-171.67
276-Jan-17
285-Jan-17
294-Jan-17
303-Jan-17
First & Last date
 
Last edited:
Upvote 0
P1=AGGREGATE(15,6,B2:B30/((M2:M30<>"")*(B2:B30>=LL2)*(B2:B30<=LM2)),1)
P2=AGGREGATE(14,6,B2:B30/((M2:M30<>"")*(B2:B30>=LL2)*(B2:B30<=LM2)),1)

<tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 12px; margin-bottom: 0px;">
</tbody>
These work great, Thank you very much for your time and help!!!

MM
 
Upvote 0
I'm curious and wanted to know something if possible. The formulae is dependent on the lowest date and the highest most current date. The current date will always be the first date listed so my question is can/how would the formulae look like if we took out the last section?


P1=AGGREGATE(15,6,B2:B30/((M2:M30<>"")*(B2:B30>=LL2)*(B2:B30<=LM2)),1)




can we remove this section
*
(B2:B30<=LM2)
) and if yes what would it now look like? I've tried a few variations but can get it to return the same value.

Thanks
MM

<tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">
</tbody>
 
Upvote 0
P1=AGGREGATE(15,6,B2:B30/((M2:M30<>"")*(B2:B30>=LL2)*(B2:B30<=LM2)),1)
P2=AGGREGATE(14,6,B2:B30/((M2:M30<>"")*(B2:B30>=LL2)*(B2:B30<=LM2)),1)

<tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 12px; margin-bottom: 0px;">
</tbody>
These work great, Thank you very much for your time and help!!!

MM
Good news. You are welcome. :)


I'm curious and wanted to know something if possible. The formulae is dependent on the lowest date and the highest most current date. The current date will always be the first date listed so my question is can/how would the formulae look like if we took out the last section?


P1=AGGREGATE(15,6,B2:B30/((M2:M30<>"")*(B2:B30>=LL2)*(B2:B30<=LM2)),1)




can we remove this section
*
(B2:B30<=LM2)
) and if yes what would it now look like? I've tried a few variations but can get it to return the same value.

Thanks
MM

<tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">
</tbody>
I don't quite understand that. In my layout above, which cell(s) contain what you are describing as the "current date"?
 
Upvote 0
Hi Peter

I was able to work it out. What I realized after seeing your formulae was that I only needed to reference the 1 date and not both. So I changed the formulae to
=AGGREGATE(15,6,B2:B30/(
(M2:M30<>"")
*
(B2:B30>=LL2)
1) Since my dates are always in order and the data I want to use is 5 years from the most current date and your formulae references this date as => so no need to make the formulae any longer than needed.


I do have a question, I tried a few variations but I was unsuccessful. What if I used the "same" formulae
=AGGREGATE(15,6,B2:B30/(
(M2:M30<>"")
*
(B2:B30>=LL2)
,1)
But rather than getting the Largest/smallest date that we instead get the average price in column M based on the date being => in cell LL2?

I currently use DAverage function and I curious if theirs a different way to get the same results.

Thanks again!
MM
 
Upvote 0
I do have a question,...
... get the average price in column M based on the date being => in cell LL2?
Is this what you mean?

=AVERAGEIF(B2:B30,">="&LL2,M2:M30)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

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