try

Sub averagelast6withvalue()
n = 0
For i = Cells(Rows.Count, "a").End(xlUp).Row To 1 Step -1
If Not IsEmpty(Cells(i, "a")) Then ' MsgBox Cells(i, "a").Address
mysum = mysum + Cells(i, "a")
n = n + 1
End If
If n = 6 Then Exit For
Next i
MsgBox mysum / 6
End Sub

--
Don Guillett
SalesAid Software

"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 :-)