I got som values in Range("A10:A100")
There are some empty cells inbetwin
The 'list' grows but only inbetwin row 10 and row 100
There are values before and after 'my' range that i dont wana use

So how do i make a AVERAGE-formula for the 6 last values in my range?

I no how to find the last not emty ROW in my range:
MAX(IF(A10:A100<"";ROW(A10:A100)))

But how to find 6 not empty cells before that cell (still inbetwin 10-100)
e.g.#.1
..............A
row.1 | empty
...........
row.8 | empty
row.9 | 3
-----------------------------
row10 | empty
row11 | 1
row12 | 3
row13 | empty
row14 | empty
row15 | 2
row16 | 4
row17 | 1
row18 | 3
row19 | 5
row20 | empty
row21 | empty
row22 | empty
..........
row100 | empty

In this e.g. i ned to add A19,A18,A17,A16,A15 and A12 (6 values)

e.g.#.2
.............A
row.1 | empty
...........
row.7 | empty
row.8 | 2
row.9 | 3
-----------------------------
row10 | empty
row11 | 1
row12 | 3
row13 | empty
row14 | empty
row15 | 2
row16 | 4
row17 | empty
row18 | empty
row19 | empty
..........
row100 | empty

In this e.g. i ned to add A16,A16,A12,A11 (4 values )
so if there is les than 6 values in my range i only use them.

I dont wana use vba, pivot, filter, or rearrange my data.
so any help would be greatfull :-)