Remove #NUM! ISERROR when using array formulas

  • Thread starter Struggling in Sheffield
  • Start date
S

Struggling in Sheffield

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.
 
T

T. Valko

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())))
 
S

Struggling in Sheffield

Hi Biff,
Thanks very much for that, tried many variations on the theme but just
couldn't hit the bullseye.
All the best.
Steve.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top