Cant get dynamic range formula to work.
 

Oh Wise Ones,
I have an array formula that I use in a workbook
to give me the average of the last 10 values in a column and it works GREAT!
I use this type of formula so I can chart it from a set range. Problem is I
tried to adapt it to another workbook and it won't work. I have data from
P7:P432 that I am try to get a moving average of the last 6 values and more
rows are added daily. I get a #NUM error. Could someone take a peek at it and
offer an opinion. Possible you may have a better formula that can handle
errors.

The formula array entered in AM7:

=INDEX(P:P,LARGE(IF($P$7:$D$65535<"",ROW($P$7:$D$ 65535)),ROW($AM$13)-ROW()))

Mike

Cant get dynamic range formula to work.
 

The formula array entered in AM7:

=INDEX(P:P,LARGE(IF($P$7:$D$65535<"",ROW($P$7:$D$ 65535)),ROW($AM$13)-ROW()))

That formula is not getting an average!

I have data from P7:P432 that I am try to get a
moving average of the last 6 values

Why does your formula reference this range - $P$7:$D$65535 - when you want
an average of this range - P7:P432 ?

Are there any empty/blank cells *within* this range - P7:P432 ?

Biff

"Mike K" wrote in message
...
Oh Wise Ones,
I have an array formula that I use in a workbook
to give me the average of the last 10 values in a column and it works
GREAT!
I use this type of formula so I can chart it from a set range. Problem is
I
tried to adapt it to another workbook and it won't work. I have data from
P7:P432 that I am try to get a moving average of the last 6 values and
more
rows are added daily. I get a #NUM error. Could someone take a peek at it
and
offer an opinion. Possible you may have a better formula that can handle
errors.

The formula array entered in AM7:

=INDEX(P:P,LARGE(IF($P$7:$D$65535<"",ROW($P$7:$D$ 65535)),ROW($AM$13)-ROW()))

Mike

Cant get dynamic range formula to work.
 

Biff,
Forgive me for my blunder. I copied the formula in a partially
modified state. It should be:
=INDEX(P:P,LARGE(IF($P$7:$P$65535<"",ROW($P$7:$P$ 65535)),ROW($AM$17)-ROW()))

I've got it working now.

Thanks,
Mike