This appears to work for me in testing:
{=AVERAGE(IF(MOD(ROW(A$1:A$500),8)=0,A$1:A$500,""))}
Note the braces surrounding the formula, indicating that this is an array formula. You do
not type them in - type in the formula
inside the braces, then to confirm the formula hit Ctrl+Shift+Enter and Excel will turn this into an array formula and you will see braces in the formula bar.
Not sure what you mean by "adjusting", but this is coded to look at every 8th line, so if you insert a row at 7, what was in row 7 becomes row 8 and becomes part of this formula.
{Edit}(a)I'm pleased as punch with myself, I came up with the same basic approach as Aladin. Not quite as fancy, but still -- woohoo!
(b) Near as I can tell, Aladin's formula averages rows 1, 9, 17, 25... whereas mine does 8, 16, 24, 32... {EndEdit}
HTH