#1   Report Post  
Posted to microsoft.public.excel.misc
Ben Dummar
 
Posts: n/a
Default #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   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default #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   Report Post  
Posted to microsoft.public.excel.misc
Ben Dummar
 
Posts: n/a
Default #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   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default #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   Report Post  
Posted to microsoft.public.excel.misc
Domenic
 
Posts: n/a
Default #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   Report Post  
Posted to microsoft.public.excel.misc
Ben Dummar
 
Posts: n/a
Default #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   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default #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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to multiply all cells in array by factor rhauff Excel Discussion (Misc queries) 2 March 21st 06 04:01 PM
Problem with Vlookup array selection Scott269 Excel Worksheet Functions 2 January 30th 06 06:29 PM
Pass an array to Rank Biff Excel Worksheet Functions 12 June 29th 05 04:15 PM
Formula to list unique values JaneC Excel Worksheet Functions 4 December 10th 04 01:25 AM
VBA Import of text file & Array parsing of that data Dennis Excel Discussion (Misc queries) 4 November 28th 04 11:20 PM


All times are GMT +1. The time now is 12:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"