Not certain what you are trying to do but see the attached
first calculation is the R12
the index would be
but you referenced the Average R12 ? average over what period? Help me understand what you really want
well the first 2 calculations will return this
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
First, thanks so much for jumping in and helping out! Second, sorry I didn't explain this well enough in my first post. Ha
So what you provided is perfect for when things are at a monthly level--it gives me that rolling average and the index looks great. However--and what I was failing to explain well--when I break it out at the daily level, it warps the rolling average and index.
What I'd like to accomplish is keeping the rolling average against the previous 12 months, but have the index apply to the rolling 12 month average no matter if the data is at the month or daily level.
I THINK that what's happening is that it's looking at periods across the table, so when I break it out to day, it's looking at the previous 12 days for an average. Is that what's happening? Any thoughts or ideas?