Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX formula returns #REF! error for ranges larger than two cells
I am using the INDEX function twice [form:
AVERAGE(INDEX(lowerlimit):INDEX(Upperlimit))] to specify a sub-range of cells within column, utilizing MATCH to find the row numbers of the lower and upper limits. It has worked beautifully before, but, mysteriously, today I am finding that the formula will give a #REF! error for any subrange larger than two cells; that is, the second index must refer to the cell following the first index, otherwise I get the error. Can anyone give me a clue as to what the problem might be, so that the formula can be fixed to return the expected value? Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX formula returns #REF! error for ranges larger than two cells
The problem is not with the fact the subrange is larger than two cells.
The only thought I have is to double check your range references to ensure Match is not returning a value that is outside of the range referenced by Index. For example =Index(A1:A10, 15) will return #REF as there are not 15 cells in the range A1:A10. If that is not the problem, you should post your exact formula instead of a general outline. "hmm" wrote: I am using the INDEX function twice [form: AVERAGE(INDEX(lowerlimit):INDEX(Upperlimit))] to specify a sub-range of cells within column, utilizing MATCH to find the row numbers of the lower and upper limits. It has worked beautifully before, but, mysteriously, today I am finding that the formula will give a #REF! error for any subrange larger than two cells; that is, the second index must refer to the cell following the first index, otherwise I get the error. Can anyone give me a clue as to what the problem might be, so that the formula can be fixed to return the expected value? Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX formula returns #REF! error for ranges larger than two cells
Just to add:
utilizing MATCH to find the row numbers Also note that the result of MATCH is *relative* to the range. -- Biff Microsoft Excel MVP "JMB" wrote in message ... The problem is not with the fact the subrange is larger than two cells. The only thought I have is to double check your range references to ensure Match is not returning a value that is outside of the range referenced by Index. For example =Index(A1:A10, 15) will return #REF as there are not 15 cells in the range A1:A10. If that is not the problem, you should post your exact formula instead of a general outline. "hmm" wrote: I am using the INDEX function twice [form: AVERAGE(INDEX(lowerlimit):INDEX(Upperlimit))] to specify a sub-range of cells within column, utilizing MATCH to find the row numbers of the lower and upper limits. It has worked beautifully before, but, mysteriously, today I am finding that the formula will give a #REF! error for any subrange larger than two cells; that is, the second index must refer to the cell following the first index, otherwise I get the error. Can anyone give me a clue as to what the problem might be, so that the formula can be fixed to return the expected value? Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX formula returns #REF! error for ranges larger than two cells
Thanks guys.
A very quirky problem: today it's working! Next time it happens I should place the entire formula, as JMB suggested. "hmm" wrote: I am using the INDEX function twice [form: AVERAGE(INDEX(lowerlimit):INDEX(Upperlimit))] to specify a sub-range of cells within column, utilizing MATCH to find the row numbers of the lower and upper limits. It has worked beautifully before, but, mysteriously, today I am finding that the formula will give a #REF! error for any subrange larger than two cells; that is, the second index must refer to the cell following the first index, otherwise I get the error. Can anyone give me a clue as to what the problem might be, so that the formula can be fixed to return the expected value? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct returns value error when ref is formula | Excel Discussion (Misc queries) | |||
Index with 2 arrays returns error | Excel Worksheet Functions | |||
Formula to sum every 4th cell returns #DIV/0! error in some column | Excel Worksheet Functions | |||
formula returns error in version 2003 only | Excel Worksheet Functions | |||
Looking for formula index/match-type that returns an array | Excel Worksheet Functions |