indirect average

[Mika]

Hy guys

I need a formula to find the average (or other function like max) in a
column of this range:

- from the last filled cell (whatever it is) in a column to X rows up.

something like: = average( lastcell_in column : offset(lastcell_in
column , -X,0))
Is that possible ?

Thanks in advance
MIka

 

[Ron Rosenfeld]

Hy guys

I need a formula to find the average (or other function like max) in a
column of this range:

- from the last filled cell (whatever it is) in a column to X rows up.

something like: = average( lastcell_in column : offset(lastcell_in
column , -X,0))
Is that possible ?

Thanks in advance
MIka

There are probably simpler formulas, but this **array** formula will work:

=AVERAGE(INDIRECT(ADDRESS(MAX(ISNUMBER(rng)*
ROW(rng))-XrowsUP,COLUMN(rng))&":"&ADDRESS(MAX(
ISNUMBER(rng)*ROW(rng)),COLUMN(rng))))

To enter an array formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula.

In the above formula,
rng is the Range to check. It cannot be the Entire column.

XrowsUp may have to be adjusted by 1 depending on exactly what you want. For
example, if you specify 10, the above might give a range of D3040 when you
really want D3140.


--ron

 

[Mika]

Thanks Ron, it worked.

Still, interested to see if there is a shortest formula...

Mika

 

[Bob Phillips]

Another just as bad

=AVERAGE(
INDEX(E1:E10,MATCH(LOOKUP(2,1/(E1:E10<>""),E1:E10),E1:E10,0)-6):
INDEX(E1:E10,MATCH(LOOKUP(2,1/(E1:E10<>""),E1:E10),E1:E10,0)))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Ron Rosenfeld]

Thanks Ron, it worked.

Still, interested to see if there is a shortest formula...

Mika

You could always write a UDF. But at least this solves the problem.
--ron

 

[Harlan Grove]

Mika wrote...
....

I need a formula to find the average (or other function like max) in a
column of this range:

- from the last filled cell (whatever it is) in a column to X rows up.

something like: = average( lastcell_in column : offset(lastcell_in
column , -X,0))

....

You could always just use OFFSET to specify the entire range.

=AVERAGE(OFFSET(E:E,MATCH(1E+300,E:E)-1,0,-x,1))

but that uses the volatile OFFSET call. If you want a nonvolatile
formula, use Bob Phillips's formula. Also, FWIW, if there could be
fewer than x nonblank cells in the column of interest, you could guard
against that error by using

=AVERAGE(OFFSET(E:E,MAX(0,MATCH(1E+300,E:E)-x),0,x,1))

 

[Mika]

Guys,

thanks a lot for all your help, super.

Rgd
Mika