Transforming values when there is missing data

[yanssen]

Hello everyone,
I have dataset with 4 rows: Date, recommendations, the number of recommendations, and stock returns. These data are given on a daily basis. However, there are some missing values in the data± Recommendations have approximately 5 missing values per year (and thus ~360 observations) and stock returns have approximately 115 missing values (and thus ~250 observations). Whenever there is a missing value for stock returns on a specific day, I would like to sum the values for recommendations on that day with the previous day(s) for which there is a stock return. For instance, as there are no stock returns on saturday and sunday (and on holidays), I would like the recommendations of a specific sunday to be summed with the recommendations of the previous saturday AND friday, and to be by the number of recommendations so this new single value can be linked to the subsequent monday.

I have attached an example file. Here, missing values are given by value 999. Column E gives the pursued result. As can be seen in Column A, there is no thursday 11 december, as my actual dataset has some missing dates for which there are no recommendations or returns.

I apologize for any mistakes.

[yanssen]

Bump. Anyone has an idea?

[oeldere]

Maybe like this?