Find the latest date for each month

cchi3k welcome to the forum.

Try this. I used a lookup table in column J and the year in cell E1 to expedite month / year look ups.

Then this formula in F2:H3

Formula:

Please Login or Register  to view this content.

The formula only works properly if the dates are in ascending order. If else this needs different formula(s).

	A	B	C	D	E	F	G	H	I	J
1	Date	DSL	GSL		2017	Date	DSL	GSL		January
2	5-Jan-17	35.06	27.68		January	19-Jan-17	34.76	27.38		February
3	13-Jan-17	34.46	27.08		February	23-Feb-17	34.96	27.58		March
4	16-Jan-17	34.46	27.08							April
5	19-Jan-17	34.76	27.38							May
6	4-Feb-17	35.36	27.98							June
7	16-Feb-17	35.36	27.98							July
8	23-Feb-17	34.96	27.58							August
9										September
10										October
11										November
12										December

Hi, welcome to the forum

See if this works for you...

	A	B	C	D	E	F
1	Date	DSL	GSL
2	5-Jan-17	35.06	27.68		1/1/2017	34.76
3	13-Jan-17	34.46	27.08		2/1/2017	34.96
4	16-Jan-17	34.46	27.08
5	19-Jan-17	34.76	27.38
6	4-Feb-17	35.36	27.98
7	16-Feb-17	35.36	27.98
8	23-Feb-17	34.96	27.58

column E contains your months (dates)
F2=INDEX($B$2:$B$8,MATCH(MAX(IF(($A$2:$A$8>=E2)*($A$2:$A$8<=EOMONTH(E2,0)),$A$2:$A$8)),$A$2:$A$8,0))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

My suggest does not need the dates sorted

The 1E+306 is a sufficiently large number guaranteed to be larger than any of the numbers in the 1/(month(...)) array. Expressed in scientific notation it is 1 followed by 306 zeros. I used it mostly out of habit. In this case 2 would have been sufficient and 2 is what I will use below.

This is how it works:

(MONTH($A$2:$A$8)=MATCH($E2,$J$1:$J$12,0))*($E$1=YEAR($A$2:$A$8)) returns an array of

{TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}*{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

When any math operator is applied to an array of TRUE / FALSE it coerces TRUE and FALSE into their underlying numeric values of 1 and 0. The result in this case looks like this:

{1;1;1;1;0;0;0}

When reciprocated 1/{1;1;1;1;0;0;0} it returns

{1;1;1;1;#DIV/0!;#DIV/0!;#DIV/0!}

For simplicity let's use 2 for the lookup value instead of 1E+306.

=LOOKUP(2,{1;1;1;1;#DIV/0!;#DIV/0!;#DIV/0!},A$2:A$8)

LOOKUP will match the largest number less than or equal to 2 that is furthest to the right in the array. Fortunately LOOKUP also ignores #DIV/0! errors. In this case LOOKUP matches the 2 to the 4th 1 in the array (which is in the fourth row). It returns the value in A2:A8 corresponding to that same row.

Since A$2:A$8 is addressed relative with respect to column A as the formula is copied across that column reference becomes column B and then C returning the corresponding the Dates, DSL and GSL prices.

BTW: You can select any step in the formula in the formula bar an press the F9 function key. You will see the arrays as presented above. You can also select any of the formula cells and use 'Evaluate formula' ... FORMULAS > Evaluate formula > click Evaluate button repeatedly. You will see a step by step presentation of how Excel evaluates the arrays in the view port.

The F9 and Evaluate formula are excellent tools for analyzing, trouble shooting formulas and self instruction. I recommend them whenever I can.

Does this help?

@ Ford
Yes. I tried to find a way to do that without an array formula. Even LOOKUP had to be committed Ctrl + Shift + Enter in all my attempts. I think I was doing something wrong.

Hi All

I'm assuming to work on yearly statistics: data in A1:C300

=SUMIF($A$1:$A$300,AGGREGATE(14,6,$A$1:$A$300/(MONTH($A$1:$A$300)=ROW($A1)),1),B$1:B$300)

or

=SUMIF($A$1:$A$300,MAX(INDEX($A$1:$A$300*(MONTH($A$1:$A$300)=ROW($A1)),)),B$1:B$300)

copied down 12 times on two columns.


Regards

Originally Posted by FlameRetired

@ Ford
Yes. I tried to find a way to do that without an array formula. Even LOOKUP had to be committed Ctrl + Shift + Enter in all my attempts. I think I was doing something wrong.

Whats wrong with using an array formula? Do you have a ton of data to go through?

No. Nothing wrong.

That's just me going through a period ... looking for non-array alternatives. I tend to over use them and when they're not necessary. Need to learn some new 'tricks'.

Originally Posted by FlameRetired

No. Nothing wrong.

That's just me going through a period ... looking for non-array alternatives. I tend to over use them and when they're not necessary. Need to learn some new 'tricks'.

oops sorry, thought that post was from the OP duh

That's OK.

It needed to be said anyway. LOL