Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Num error in Array
Hello,
I have the following array in the first cell. The array works great on rows 1-3: =INDEX(GCData!AM:AM,SMALL(IF(GCData!AN$1:AN$5=1,RO W($1:$5)),ROW(1:1))) when it reaches the 4th occurences it gives the #num error, below is the code in the 4th cell or row. =INDEX(GCData!AM:AM,SMALL(IF(GCData!AN$1:AN$5=1,RO W($1:$5)),ROW(4:4))) What can I do to fix the array? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Num error in Array
Hi!
There's nothing wrong with your formula. Are you sure the 4th occurrence is a match? What does this return: =COUNTIF(GCData!AN$1:AN$5,1) The 4th instance may be a TEXT value? Biff "Ben Dummar" wrote in message ... Hello, I have the following array in the first cell. The array works great on rows 1-3: =INDEX(GCData!AM:AM,SMALL(IF(GCData!AN$1:AN$5=1,RO W($1:$5)),ROW(1:1))) when it reaches the 4th occurences it gives the #num error, below is the code in the 4th cell or row. =INDEX(GCData!AM:AM,SMALL(IF(GCData!AN$1:AN$5=1,RO W($1:$5)),ROW(4:4))) What can I do to fix the array? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Num error in Array
Biff,
It returns the number "3". The cells in column AN display the following? AN3:AN6 1 AN7 AN8 1 AN9 AN10 1 AN11 AN12:AN19 1 ..... "Biff" wrote: Hi! There's nothing wrong with your formula. Are you sure the 4th occurrence is a match? What does this return: =COUNTIF(GCData!AN$1:AN$5,1) The 4th instance may be a TEXT value? Biff "Ben Dummar" wrote in message ... Hello, I have the following array in the first cell. The array works great on rows 1-3: =INDEX(GCData!AM:AM,SMALL(IF(GCData!AN$1:AN$5=1,RO W($1:$5)),ROW(1:1))) when it reaches the 4th occurences it gives the #num error, below is the code in the 4th cell or row. =INDEX(GCData!AM:AM,SMALL(IF(GCData!AN$1:AN$5=1,RO W($1:$5)),ROW(4:4))) What can I do to fix the array? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Num error in Array
It returns the number "3".
Ok, if that formula returns 3 that means there isn't a 4th instance in the range A1:A5 causing the #NUM! error. If you want to pick up the 4th instance you need to extend your range. Biff "Ben Dummar" wrote in message ... Biff, It returns the number "3". The cells in column AN display the following? AN3:AN6 1 AN7 AN8 1 AN9 AN10 1 AN11 AN12:AN19 1 .... "Biff" wrote: Hi! There's nothing wrong with your formula. Are you sure the 4th occurrence is a match? What does this return: =COUNTIF(GCData!AN$1:AN$5,1) The 4th instance may be a TEXT value? Biff "Ben Dummar" wrote in message ... Hello, I have the following array in the first cell. The array works great on rows 1-3: =INDEX(GCData!AM:AM,SMALL(IF(GCData!AN$1:AN$5=1,RO W($1:$5)),ROW(1:1))) when it reaches the 4th occurences it gives the #num error, below is the code in the 4th cell or row. =INDEX(GCData!AM:AM,SMALL(IF(GCData!AN$1:AN$5=1,RO W($1:$5)),ROW(4:4))) What can I do to fix the array? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Num error in Array
It looks like you need to adjust the range for Column AN. Assuming that
the formula is entered in AO1 and copied down, try... =INDEX(GCData!AM$3:AM$19,SMALL(IF(GCData!AN$3:AN$1 9=1,ROW(GCData!AN$3:AN$ 19)-ROW(GCData!AN$3)+1),ROWS(AO$1:AO1))) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , Ben Dummar wrote: Biff, It returns the number "3". The cells in column AN display the following? AN3:AN6 1 AN7 AN8 1 AN9 AN10 1 AN11 AN12:AN19 1 .... "Biff" wrote: Hi! There's nothing wrong with your formula. Are you sure the 4th occurrence is a match? What does this return: =COUNTIF(GCData!AN$1:AN$5,1) The 4th instance may be a TEXT value? Biff "Ben Dummar" wrote in message ... Hello, I have the following array in the first cell. The array works great on rows 1-3: =INDEX(GCData!AM:AM,SMALL(IF(GCData!AN$1:AN$5=1,RO W($1:$5)),ROW(1:1))) when it reaches the 4th occurences it gives the #num error, below is the code in the 4th cell or row. =INDEX(GCData!AM:AM,SMALL(IF(GCData!AN$1:AN$5=1,RO W($1:$5)),ROW(4:4))) What can I do to fix the array? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Num error in Array
Biff,
I must have something else wrong then becuase it displays an 1 in the 4 row which would create the 4th instance. Is not reading it as a number somehow thus causing the error? "Biff" wrote: It returns the number "3". Ok, if that formula returns 3 that means there isn't a 4th instance in the range A1:A5 causing the #NUM! error. If you want to pick up the 4th instance you need to extend your range. Biff "Ben Dummar" wrote in message ... Biff, It returns the number "3". The cells in column AN display the following? AN3:AN6 1 AN7 AN8 1 AN9 AN10 1 AN11 AN12:AN19 1 .... "Biff" wrote: Hi! There's nothing wrong with your formula. Are you sure the 4th occurrence is a match? What does this return: =COUNTIF(GCData!AN$1:AN$5,1) The 4th instance may be a TEXT value? Biff "Ben Dummar" wrote in message ... Hello, I have the following array in the first cell. The array works great on rows 1-3: =INDEX(GCData!AM:AM,SMALL(IF(GCData!AN$1:AN$5=1,RO W($1:$5)),ROW(1:1))) when it reaches the 4th occurences it gives the #num error, below is the code in the 4th cell or row. =INDEX(GCData!AM:AM,SMALL(IF(GCData!AN$1:AN$5=1,RO W($1:$5)),ROW(4:4))) What can I do to fix the array? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Num error in Array
See Domenic's reply. I think he may have figured it out. Your original post
uses the range of AN1:AN5 but then your follow-up looks like the range is AN3:AN19. If that doesn't solve the problem post back with the EXACT details of the ranges you're using AND the EXACT formula you're using. Biff "Ben Dummar" wrote in message ... Biff, I must have something else wrong then becuase it displays an 1 in the 4 row which would create the 4th instance. Is not reading it as a number somehow thus causing the error? "Biff" wrote: It returns the number "3". Ok, if that formula returns 3 that means there isn't a 4th instance in the range A1:A5 causing the #NUM! error. If you want to pick up the 4th instance you need to extend your range. Biff "Ben Dummar" wrote in message ... Biff, It returns the number "3". The cells in column AN display the following? AN3:AN6 1 AN7 AN8 1 AN9 AN10 1 AN11 AN12:AN19 1 .... "Biff" wrote: Hi! There's nothing wrong with your formula. Are you sure the 4th occurrence is a match? What does this return: =COUNTIF(GCData!AN$1:AN$5,1) The 4th instance may be a TEXT value? Biff "Ben Dummar" wrote in message ... Hello, I have the following array in the first cell. The array works great on rows 1-3: =INDEX(GCData!AM:AM,SMALL(IF(GCData!AN$1:AN$5=1,RO W($1:$5)),ROW(1:1))) when it reaches the 4th occurences it gives the #num error, below is the code in the 4th cell or row. =INDEX(GCData!AM:AM,SMALL(IF(GCData!AN$1:AN$5=1,RO W($1:$5)),ROW(4:4))) What can I do to fix the array? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to multiply all cells in array by factor | Excel Discussion (Misc queries) | |||
Problem with Vlookup array selection | Excel Worksheet Functions | |||
Pass an array to Rank | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |