hi Biff. tks. for reply
ur formula coms close to what im looking for.
do u think u can modify it, so it wil ignore 0 (cero) and text too ?
sry i didnt say that befor but i didnt no then that they could be present
in my list-range

"Biff" skrev:

Try this:

Array entered:

=AVERAGE(A100:INDEX(A10:A100,LARGE(IF(A10:A100,ROW (A10:A100)-ROW(A10)+1),MIN(6,COUNT(A10:A100)))))

Note: if there are *no" numbers in the range you'll get a #NUM! error.

Biff

"excelent" wrote in message
...
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 :-)