Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
array formula with a dynamic range. | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Setting dynamic range in a formula | Excel Worksheet Functions | |||
Help to adapt Formula syntax to work with Dynamic Named Ranges | Excel Worksheet Functions | |||
formula to set up dynamic range in names | Excel Worksheet Functions |