Find last day of month's data in a non contiguous data series?

P

Philip041

Board Regular
Joined
Jul 17, 2011
Messages
63
Hello,

I have a financial index series. It is priced daily except it misses weekends bank holidays etc. ie. I can't predict what the last data point will be in each month.

I want to pull the last point available in each month so I can make a monthly series, is this possible? Unfortunately some of the series I have are pretty obscure so I have only managed to find these series instead of monthly ones.

Many thanks in advance!
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Jon von der Heyden said:
Can you please post a sample for us to refer to?
Click to expand...

Yeh sure, here is some Sensex data

21/05/201216183.26
22/05/201216026.41
23/05/201215948.1
24/05/201216222.3
25/05/201216217.82
28/05/201216416.84
29/05/201216438.58
30/05/201216312.15
31/05/201216218.53
01/06/201215965.16
04/06/201215988.4
05/06/201216020.64
06/06/201216454.3
07/06/201216649.05
08/06/201216718.87
11/06/201216668.01
12/06/201216862.8
13/06/201216880.51
14/06/201216677.88
15/06/201216949.83
18/06/201216705.83
19/06/201216859.8
20/06/201216896.63
21/06/201217032.56
22/06/201216972.51
25/06/201216882.16
26/06/201216906.58
27/06/201216967.76
28/06/201216990.76
29/06/201217429.98
02/07/201217398.98
03/07/201217421.04

<tbody>
</tbody>

So for May it is just the last day in the month (31st) but in June the last data point available is on the 29th, I have this data going back many years and for loads of indices so wanted to be able to get the last data point of each month if that makes sense?
 
Upvote 0
Excel Workbook
ABCDEFGHI
1datesist of monthmax from range a2:a?
227-Jan-1101-Jan-1129-Jan-11
328-Jan-1101-Feb-1128-Feb-11
429-Jan-1101-Mar-1130-Mar-11
526-Feb-1101-Apr-1129-Apr-11
627-Feb-1101-May-1129-May-11
728-Feb-1101-Jun-1128-Jun-11
828-Mar-1101-Jul-1128-Jul-11
929-Mar-1101-Aug-1127-Aug-11
1030-Mar-1101-Sep-1126-Sep-11
1127-Apr-1101-Oct-1126-Oct-11
1228-Apr-1101-Nov-1125-Nov-11
1329-Apr-1101-Dec-1125-Dec-11
1427-May-1101-Jan-1224-Jan-12
1528-May-1101-Feb-1223-Feb-12
1629-May-1101-Mar-1224-Mar-12
1726-Jun-11continues down a bit !01-Apr-1223-Apr-12
1827-Jun-11v01-May-1223-May-12
1928-Jun-11v01-Jun-1221-Jun-12
2026-Jul-11v
Sheet1
 
Upvote 0
martindwilson said:
Sheet1

ABCDEFGHI
1dates ist of monthmax from range a2:a?
227-Jan-11 01-Jan-1129-Jan-11
328-Jan-11 01-Feb-1128-Feb-11
429-Jan-11 01-Mar-1130-Mar-11
526-Feb-11 01-Apr-1129-Apr-11
627-Feb-11 01-May-1129-May-11
728-Feb-11 01-Jun-1128-Jun-11
828-Mar-11 01-Jul-1128-Jul-11
929-Mar-11 01-Aug-1127-Aug-11
1030-Mar-11 01-Sep-1126-Sep-11
1127-Apr-11 01-Oct-1126-Oct-11
1228-Apr-11 01-Nov-1125-Nov-11
1329-Apr-11 01-Dec-1125-Dec-11
1427-May-11 01-Jan-1224-Jan-12
1528-May-11 01-Feb-1223-Feb-12
1629-May-11 01-Mar-1224-Mar-12
1726-Jun-11continues down a bit ! 01-Apr-1223-Apr-12
1827-Jun-11v 01-May-1223-May-12
1928-Jun-11v 01-Jun-1221-Jun-12
2026-Jul-11v

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:71px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:71px;"><col style="width:68px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
H2{=MAX(IF((MONTH($A$2:$A$54)=MONTH(G2))*(YEAR($A$2:$A$54)=YEAR(G2)),$A$2:$A$54))}
H3{=MAX(IF((MONTH($A$2:$A$54)=MONTH(G3))*(YEAR($A$2:$A$54)=YEAR(G3)),$A$2:$A$54))}
H4{=MAX(IF((MONTH($A$2:$A$54)=MONTH(G4))*(YEAR($A$2:$A$54)=YEAR(G4)),$A$2:$A$54))}
H5{=MAX(IF((MONTH($A$2:$A$54)=MONTH(G5))*(YEAR($A$2:$A$54)=YEAR(G5)),$A$2:$A$54))}
H6{=MAX(IF((MONTH($A$2:$A$54)=MONTH(G6))*(YEAR($A$2:$A$54)=YEAR(G6)),$A$2:$A$54))}
H7{=MAX(IF((MONTH($A$2:$A$54)=MONTH(G7))*(YEAR($A$2:$A$54)=YEAR(G7)),$A$2:$A$54))}
H8{=MAX(IF((MONTH($A$2:$A$54)=MONTH(G8))*(YEAR($A$2:$A$54)=YEAR(G8)),$A$2:$A$54))}
H9{=MAX(IF((MONTH($A$2:$A$54)=MONTH(G9))*(YEAR($A$2:$A$54)=YEAR(G9)),$A$2:$A$54))}
H10{=MAX(IF((MONTH($A$2:$A$54)=MONTH(G10))*(YEAR($A$2:$A$54)=YEAR(G10)),$A$2:$A$54))}
H11{=MAX(IF((MONTH($A$2:$A$54)=MONTH(G11))*(YEAR($A$2:$A$54)=YEAR(G11)),$A$2:$A$54))}
H12{=MAX(IF((MONTH($A$2:$A$54)=MONTH(G12))*(YEAR($A$2:$A$54)=YEAR(G12)),$A$2:$A$54))}
H13{=MAX(IF((MONTH($A$2:$A$54)=MONTH(G13))*(YEAR($A$2:$A$54)=YEAR(G13)),$A$2:$A$54))}
H14{=MAX(IF((MONTH($A$2:$A$54)=MONTH(G14))*(YEAR($A$2:$A$54)=YEAR(G14)),$A$2:$A$54))}
H15{=MAX(IF((MONTH($A$2:$A$54)=MONTH(G15))*(YEAR($A$2:$A$54)=YEAR(G15)),$A$2:$A$54))}
H16{=MAX(IF((MONTH($A$2:$A$54)=MONTH(G16))*(YEAR($A$2:$A$54)=YEAR(G16)),$A$2:$A$54))}
H17{=MAX(IF((MONTH($A$2:$A$54)=MONTH(G17))*(YEAR($A$2:$A$54)=YEAR(G17)),$A$2:$A$54))}
H18{=MAX(IF((MONTH($A$2:$A$54)=MONTH(G18))*(YEAR($A$2:$A$54)=YEAR(G18)),$A$2:$A$54))}
H19{=MAX(IF((MONTH($A$2:$A$54)=MONTH(G19))*(YEAR($A$2:$A$54)=YEAR(G19)),$A$2:$A$54))}

<tbody>
</tbody>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...

Cheers but I don't follow? Can you explain?
 
Upvote 0
alternative just enter start year
Excel Workbook
ABCDEFGHI
1dates2011
227-Jan-1129-Jan-11
328-Jan-1128-Feb-11
429-Jan-1130-Mar-11
526-Feb-1129-Apr-11
627-Feb-1129-May-11
728-Feb-1128-Jun-11
828-Mar-1128-Jul-11
929-Mar-1127-Aug-11
1030-Mar-1126-Sep-11
1127-Apr-1126-Oct-11
1228-Apr-1125-Nov-11
1329-Apr-1125-Dec-11
1427-May-1124-Jan-12
1528-May-1123-Feb-12
1629-May-1124-Mar-12
1726-Jun-11continues down a bit !23-Apr-12
1827-Jun-11v23-May-12
1928-Jun-11v21-Jun-12
Sheet1
 
Upvote 0
Maybe I could put the data in a table and set a conditional rule which highlights the latest available month and then filter for that colour?
 
Upvote 0
Given your sample:

Excel 2010
ABCDE
1# of months3
2DateAmountMonthAmount
321/05/201216183.331/05/201216218.5
422/05/201216026.429/06/201217430
523/05/201215948.103/07/201217421
624/05/201216222.3
725/05/201216217.8
828/05/201216416.8
929/05/201216438.6
1030/05/201216312.2
1131/05/201216218.5
1201/06/201215965.2
1304/06/201215988.4
1405/06/201216020.6
1506/06/201216454.3
1607/06/201216649.1
1708/06/201216718.9
1811/06/201216668
1912/06/201216862.8
2013/06/201216880.5
2114/06/201216677.9
2215/06/201216949.8
2318/06/201216705.8
2419/06/201216859.8
2520/06/201216896.6
2621/06/201217032.6
2722/06/201216972.5
2825/06/201216882.2
2926/06/201216906.6
3027/06/201216967.8
3128/06/201216990.8
3229/06/201217430
3302/07/201217399
3403/07/201217421

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
E1=MONTH(MAX(A3:A34))-MONTH(MIN(A3:A34))+1
E3=IF(ROWS($3:3)<=$E$1,VLOOKUP(D3,$A$3:$B$34,2,1),"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
D3{=IF(ROWS($3:3)<=$E$1,MAX(IF(MONTH($A$3:$A$34)=MONTH(SMALL(IF($A$3:$A$34>N(D2),$A$3:$A$34),1)),$A$3:$A$34)),"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Upvote 0
Jon von der Heyden said:
Given your sample:

Excel 2010
ABCDE
1# of months3
2DateAmountMonthAmount
321/05/201216183.331/05/201216218.5
422/05/201216026.429/06/201217430
523/05/201215948.103/07/201217421
624/05/201216222.3
725/05/201216217.8
828/05/201216416.8
929/05/201216438.6
1030/05/201216312.2
1131/05/201216218.5
1201/06/201215965.2
1304/06/201215988.4
1405/06/201216020.6
1506/06/201216454.3
1607/06/201216649.1
1708/06/201216718.9
1811/06/201216668
1912/06/201216862.8
2013/06/201216880.5
2114/06/201216677.9
2215/06/201216949.8
2318/06/201216705.8
2419/06/201216859.8
2520/06/201216896.6
2621/06/201217032.6
2722/06/201216972.5
2825/06/201216882.2
2926/06/201216906.6
3027/06/201216967.8
3128/06/201216990.8
3229/06/201217430
3302/07/201217399
3403/07/201217421

<tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
E1=MONTH(MAX(A3:A34))-MONTH(MIN(A3:A34))+1
E3=IF(ROWS($3:3)<=$E$1,VLOOKUP(D3,$A$3:$B$34,2,1),"")

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
D3{=IF(ROWS($3:3)<=$E$1,MAX(IF(MONTH($A$3:$A$34)=MONTH(SMALL(IF($A$3:$A$34>N(D2),$A$3:$A$34),1)),$A$3:$A$34)),"")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
Click to expand...


That is so awesome! Thanks! How on earth did you think of that?
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Row Summary: First, Last, Contiguous, Non-contiguous
Replies
1
Views
251
Marcelo Branco
M
A
  • Question
Convert randomly dated entries to monthly and/or daily total
Replies
6
Views
949
ebea
ebea
J
  • Question
VBA to find and locate #REF! within excel chart series source data / formulae
Replies
8
Views
2K
Alex Blakenburg
A
V
  • Solved
Find the first and last non-zero numbers in a row and subtract them
2
Replies
10
Views
2K
vladimiratanasiu
V
T
  • Question
Macro to move chart data series reference down one row for all charts and all series in workbook.
2
Replies
16
Views
4K
Jon Peltier
J

Forum statistics

Threads
1,203,234
Messages
6,054,276
Members
444,714
Latest member
excel2782

Share this page

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