Remove #NUM! ISERROR when using array formulas

Hi all,
I’m using the following array formula to copy certain rows of data from a
master table into smaller tables located below the master:

{etc}

{=INDIRECT(ADDRESS(LARGE(IF($C$3:$C$902=C913,ROW($C$3:$C$902)),3),COLUMN()))}

{=INDIRECT(ADDRESS(LARGE(IF($C$3:$C$902=C913,ROW($C$3:$C$902)),2),COLUMN()))}

{=INDIRECT(ADDRESS(LARGE(IF($C$3:$C$902=C913,ROW($C$3:$C$902)),1),COLUMN()))}

I can then do various calculations using the smaller tables.

However where the smaller tables aren’t completely filled with data I’m
getting a #NUM! error, which unfortunately plays havoc with some of my
calculations.

Normally I would just use something like the formula below to leave any
error cells blank:

=IF(ISERROR(FORMULA),””,FORMULA)

However I’m not sure how to marry the two together when using an array
formula.

Would appreciate any help to break the impasse.

Cheers,
Steve.

The portion of the formula that will generate the #NUM! error is:

LARGE(IF($C$3:$C$902=C913,ROW($C$3:$C$902)),n)

Where n = instance number. If there isn't an nth instance then you get
#NUM!.

So, you can just trap that portion like this (still array entered):

=IF(ISERROR(LARGE(IF($C$3:$C$902=C913,ROW($C$3:$C$902)),n)),"",INDIRECT(ADDRESS(LARGE(IF($C$3:$C$902=C913,ROW($C$3:$C$902)),n),COLUMN())))

--
Biff
Microsoft Excel MVP


"Struggling in Sheffield" <StrugglinginSheffield.DeleteThis@discussions.microsoft.com>
wrote in message news:C5B50D07-41AC-42AC-BB53-E61A22DCBCF3@microsoft.com...
> Hi all,
> I'm using the following array formula to copy certain rows of data from a
> master table into smaller tables located below the master:
>
> {etc}
>
> {=INDIRECT(ADDRESS(LARGE(IF($C$3:$C$902=C913,ROW($C$3:$C$902)),3),COLUMN()))}
>
> {=INDIRECT(ADDRESS(LARGE(IF($C$3:$C$902=C913,ROW($C$3:$C$902)),2),COLUMN()))}
>
> {=INDIRECT(ADDRESS(LARGE(IF($C$3:$C$902=C913,ROW($C$3:$C$902)),1),COLUMN()))}
>
> I can then do various calculations using the smaller tables.
>
> However where the smaller tables aren't completely filled with data I'm
> getting a #NUM! error, which unfortunately plays havoc with some of my
> calculations.
>
> Normally I would just use something like the formula below to leave any
> error cells blank:
>
> =IF(ISERROR(FORMULA),"",FORMULA)
>
> However I'm not sure how to marry the two together when using an array
> formula.
>
> Would appreciate any help to break the impasse.
>
> Cheers,
> Steve.
>