AVERAGE(with complicated range -blanks..)

[excelent]

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

 

[Don Guillett]

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

 

[Biff]

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]

hi don. tks. for reply, it dosent sems to return the rigth average value
and i prefer a excel-formula - bif's is close to what im looking for
just ned it to ignore 0 and text too - ill tell him now
so if u got a formula to fix that im all ears lol.

"Don Guillett" skrev:

 

[excelent]

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:

 

[Domenic]

Try...

=AVERAGE(IF(ROW(A10:A100)>=LARGE(IF(ISNUMBER(A10:A100),IF(A10:A100>0,ROW(
A10:A100))),MIN(6,COUNTIF(A10:A100,">0"))),IF(ISNUMBER(A10:A100),IF(A10:A
100>0,A10:A100))))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Domenic]

Actually, the last ISNUMBER condition is not necessary. The following
should suffice...

=AVERAGE(IF(ROW(A10:A100)>=LARGE(IF(ISNUMBER(A10:A100),IF(A10:A100>0,ROW(
A10:A100))),MIN(6,COUNTIF(A10:A100,">0"))),IF(A10:A100>0,A10:A100)))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[excelent]

wohoo Domenic ur the mann did that help ?.. oh yeaaah great job




"Domenic" skrev: