array formula nested in non-array formula

[Monte Manning]

I'm trying to nest an array formula within a complext non-
array formula.. specifically..

=hyperlink(<arg1>,if(<cond1>,"X",<array formula>))

Since array formula require cntl+shift+enter, it ends up
making the entire expression an array formula and returns
the incorrect result. Of course using a simple "enter"
does not work either since the array formula is not
inserted.

Any ideas??

Thanx,

-Monte-

 

[Bernie Deitrick]

Monte,

Put the array formula into it's own cell and reference it in your
non-array formula.

HTH,
Bernie
MS Excel MVP

 

[Guest]

I considered that, but the worksheet I'm building has a
LARGE number of these cells replicated (like a few
thousand). I was hoping to have a "clean" solution (eg:
incorporating it into the formula). Do you know if it's
possible to do that?

Thanx for the help!

-M-

 

[Dave R.]

I'll horn in, and say that there are results of SOME formulas that are
entered with C-S-E which can be obtained with formulas that do not use
C-S-E, and one would probably need to know the specific array formula you
are using to proceed from here.

 

[Monte]

Here's the beast...

From the "SUMIF" on, (in the formula below this sentance),
I want to replace with the array formula AVERAGE(IF
(Data_Probe_T26Z!$A:$A=Lot_Num,INDIRECT(AC$1)))

=HYPERLINK("http://yada-yada"&LEFT(Lot_Num,9)
&"_"&SWR&"more-yada"&Lot_Num&"&swr="&SWR&"&test=!"&AC$4,IF
(ISERROR(AC$2),AC$1,IF(ISERROR(MATCH
(Lot_Num+0,Data_Probe_T26Z!$A:$A,0)),"X",SUMIF
(Data_Probe_T26Z!$A:$A,"="&Lot_Num,INDIRECT(AC$1))/COUNTIF
(Data_Probe_T26Z!$A:$A,"="&Lot_Num))))

Lot_Num, SWR are arrays.. which i just realized might hork
this up.. so I changed them to relative references to a
single cell.. This (i think) moved me a little closer to a
solution, but now I think the array in the non-array-
formula MATCH might be the issue. I'm not sure I have a
work-around for that one.. I get the #NUM error (whereas
before.. it returned "X" as if the ISERROR was returning a
TRUE when in fact it should be FALSE (as it is in the non-
array-formula version)).

Thanx,

-Monte-