I need a dynamic formula moving average based on the date received

Dear All,

I am working on building a formula to calculate the moving average based on the vendor's name and the date the actual report was received.

In the attached report, there are two tabs: "Accrual" and "Database". In the "Database" tab:

Column A contains the respective vendor names.
Column B contains the "Date of Actual Report Received," which is a crucial date for this calculation.

When performing month-end closings, I need to calculate the moving average of the last 3 months of actual figures. For example, if I am closing for January 2025, I would typically have received December's report in January. However, the report could sometimes arrive later than the 31st of January. In this case, I should not include the December report in the moving average calculation. Instead, the moving average would be based on the figures for September, October, and November.

For instance, for vendor KG-US in the "Accrual" tab, cell B2 should show $2181.09. This figure should be the average of the actual amounts from September, October, and November, based on the date the actual report was received, which is indicated in cell F2 of the "Database" tab.

Please help me with the formula in "Accrual" tab please.

Thanks so much for the formula!!! it works.