Find value for last day of month with data

[maacmaac]

I have a table with daily data for past three years. I trying to find an easy way to find the value for the last day of each month with data and populate a monthly table.

For example, the last three days of Jan-2008 are as follows:
1/29 = 5000
1/30 = 5500
1/31 = 6000

The last three days of May-2008 are as follows:
5/29 = 12000
5/30 = 13000
5/31 =

For this example, I want to populate the closing value for each month. For January, the closing value is 6000; for May, the closing value is 13000 (there is no data for 3/31, so it uses the next prior day with data.

Attached is sample of data table and monthly table (monthly table includes sample of desired output). Thank you in advance for any comments.

[NBVC]

In B4 try:

Please Login or Register  to view this content.

copied across,

In C4:

Please Login or Register  to view this content.

copied across

in D4:

Please Login or Register  to view this content.

copied across

[maacmaac]

Excellent! You saved me gobs of time by not having to do this manually. It will probably take me an entire day to figure out how it works. If you don't mind, how does the "9.9999999E+307" fit into the formula - I can't figure that part out as I have never seen it before.

Thanks again.

[NBVC]

Basically, 9.9999999e+307 is the biggest number that Excel recognizes...

Lookup() looks up the last position a value is smaller than or equal to the lookup value.. so we are looking for that big number (9.9999999e+307)

The Choose() function allows us to create a 2-dimensional array consisting of 0 and the result of the Lookup(2/1...) function.

If the Lookup results in an error, then 0 is the last time the values in the array is smaller than or equal to the big number. If a number is returned by the Lookup(2/1...) function, then that number is the last time a value in the array is smaller than or equal to the big number, so that is returned.

[maacmaac]

Thanks for the explanation. I appreciate your comments.