Hi All
I'm stumped on an offset formula
I need to average the value of the last 12 payments excluding any months with a zero/blank, capped at counting back 24 months.
So for example at March 2020 I need to average the last 12 payments. On a countback the furthest I could go back on the formula would be to Include Apr 2018. However of there were only 10 payments over the 24 months then only need to average the 10 payments
This needs to be a rolling formula with data added to bottom of table
On my data the months are column c, with the values in column F
I can do basic offset but when I add -12 to rows it counts the blanks and zeros and just adds the first 12 rows up
Thanks
I'm stumped on an offset formula
I need to average the value of the last 12 payments excluding any months with a zero/blank, capped at counting back 24 months.
So for example at March 2020 I need to average the last 12 payments. On a countback the furthest I could go back on the formula would be to Include Apr 2018. However of there were only 10 payments over the 24 months then only need to average the 10 payments
This needs to be a rolling formula with data added to bottom of table
On my data the months are column c, with the values in column F
I can do basic offset but when I add -12 to rows it counts the blanks and zeros and just adds the first 12 rows up
Thanks