Average with Offsets

[montanaaggie]

I want to take an average of the last three in a column. The problem is sometimes there are blanks and the equation I am using in multiple tabs is not working. If the cells are not blank the equation works fine
=AVERAGE(OFFSET('Tab 1'!$C$14,COUNT('Tab 1'!$C:$C),0,-3))/1000


In this example when the rows are blank it is average the cells highlighted in yellow. I would like it to be the cells highlighted in blue

 

[arthurbr]

Like this ? Excel formula: Average last N values in a table | Exceljet
(OFFSET is a volatile function, best to avoid it)

 

[montanaaggie]

That index function didn't work as I only have a column of data and not a table. I tried it with the single column but it errored out.

 

[arthurbr]

And why can't you make your range a table? You will probably add data in the future, so using an Excel Table will really be a bonus
This being said, something like

=AVERAGE(INDEX(A1:A6,ROWS(A1:A6)-2):INDEX(A1:A6,ROWS(A1:A6)))

works
(adapt range as needed)