#Num error in Array

[Ben Dummar]

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,ROW($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,ROW($1:$5)),ROW(4:4)))

What can I do to fix the array?

[Biff]

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" <[email protected]> wrote in message
news:[email protected]...
> 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,ROW($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,ROW($1:$5)),ROW(4:4)))
>
> What can I do to fix the array?

[Ben Dummar]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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,ROW($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,ROW($1:$5)),ROW(4:4)))
> >
> > What can I do to fix the array?
>
>
>

[Biff]

>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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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,ROW($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,ROW($1:$5)),ROW(4:4)))
>> >
>> > What can I do to fix the array?
>>
>>
>>

[Domenic]

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$19=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 <[email protected]>,
Ben Dummar <[email protected]> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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,ROW($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,ROW($1:$5)),ROW(4:4)))
> > >
> > > What can I do to fix the array?
> >
> >
> >

[Ben Dummar]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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,ROW($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,ROW($1:$5)),ROW(4:4)))
> >> >
> >> > What can I do to fix the array?
> >>
> >>
> >>
>
>
>

[Biff]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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,ROW($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,ROW($1:$5)),ROW(4:4)))
>> >> >
>> >> > What can I do to fix the array?
>> >>
>> >>
>> >>
>>
>>
>>