Rolling average using offset that ignores blanks

[jd33a]

I am looking to calculate a dynamic rolling average that ignores blank cells. Currently my formula is average(offset(L1, count(L2:L82),,-20)) but this is averaging blank cells that are present in my data. Ideally I would be able to average the last 20 non-blank values in this list.

Appreciate the help, thank you!

[oeldere]

Always difficult to answer without an small excel file, with manualy added the result.

I would solving that using pivot table.