|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
October 12th, 2009, 09:59 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
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. 
|
|
#2
October 13th, 2009, 03:10 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Remove #NUM! ISERROR when using array formulas
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" wrote in message ... 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.
|
|
#3
October 15th, 2009, 04:08 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Remove #NUM! ISERROR when using array formulas
Hi Biff,
Thanks very much for that, tried many variations on the theme but just couldn't hit the bullseye. All the best. Steve. "T. Valko" wrote: 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" wrote in message ... 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.
|
|
#4
October 15th, 2009, 05:26 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Remove #NUM! ISERROR when using array formulas
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Struggling in Sheffield" wrote in message ... Hi Biff, Thanks very much for that, tried many variations on the theme but just couldn't hit the bullseye. All the best. Steve. "T. Valko" wrote: 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" wrote in message ... 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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
