Remaining Month's average rate

[morassuttim]

Hey,

looking for for some help to write a formula that will calculate the remaining average rate that filers out the previous month.

For example:

The total average rate from April 2024 to March 2025 is 4.30 
The total average rate from May 2024 to March 2025 is 4.32

 The total average rate from June 2024 to March 2025 is 4.33

I attached an excel file that has the rate data and month data

thank you!!!

[AGilbert · Accepted solution]

I put together a batch macro that operates as follows:

1. Passes a RecordID as an identifier to the batch macro via the control parameter.
2. Filters for all records with a date greater than or equal to itself and within the next 12 months. 
3. Appends an average and count of records (for the sake of your review) to the original record.
4. Returns the record and appends to the previous in main.

This macro does not summarize by month as your note above suggests ("The total average rate from April 2024 to March 2025 is 4.30"). However, the first occurrence of each month ties to your expected values. Depending on your use case you may need to include a summarization by month.

Please use the count variable to review that the correct records are included. Your data only included one year of records which is why the count steadily declines. 

Overview is below. Hope this helps and let me know if you've got any more questions. 

-ag

[morassuttim]

this is very helpful and works for me. thank you for your help!!