array formula nested in non-array formula

  • Thread starter Thread starter Monte Manning
  • Start date Start date
M

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-
 
Monte,

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

HTH,
Bernie
MS Excel MVP
 
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-
 
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.
 
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-
 
Back
Top