I have this Excel sheet with daily prices of some stocks. I want to calculate monthly returns from December 2017 to December 2019 with the last days of the month that the market was open. So I need to "erase" all the prices that are not from the last day of each month I have tried using max function with filter function, but I'm a noobie on Excel so I don't really know how to use them. Here is how the excel sheet looks like:

Hope you can help me, I want to learn and I wouldn't like to do it manually :)

CodePudding user response：

You could just add another column and use EOMONTH like this =A2=EOMONTH(A2,0) and then just filter by TRUE

CodePudding user response：

Use a helper column where you compare the result of the MONTH() function for the current row with the result of the MONTH() function for the following row: