"Jerry W. Lewis" wrote in message
...
"T. Valko" wrote:

Here's what's happening but I'm not sure why...

When entered in a single cell...

COUNT(formula)
COUNTA(formula)

MMULT evaluates to #VALUE!.

So you're getting:

#VALUE!-$C$2:$C$13 which returns an array of 12 #VALUE! errors
...

Thanks, that was a useful observation which I had not noticed. My endgame
was to calculate =SUMSQ(formula), the residual sum of squares for
nonlinear
regression (of a four parameter logistic function) involving two
conditionally linear parameters that are estimated by LINEST given trial
values of the other parameters (to reduce the dimensionality of the
nonlinear
minimization problem). The COUNT and COUNTA dichotomy was part of my
attempt
to debug why I was getting #VALUE! from a formula that appeared to be
correct, and worked correctly in all components, but not as a whole. It
would be so much easier if MS would be more consistent in their
implementation of array formula processing (or else would clearly document
their consistency).

Still not sure why, but INDEX seems to be the culprit here. INDEX entered
early in the process to display the coefficients in the original order of
predictor columns (presumably the order reversal in LINEST is for
sequential
model selection purposes, where the coefficient to be tested will always
be
the first output column, but it sure is a pain for most other purposes).
Since the use of INDEX arose naturally in the development, I never
considered
alternatives, but
=MMULT({0,1,0}+{1,-1,0}/(1+(B$31/$D$2:$D$13)^$A32),TRANSPOSE(LINEST($C$2:$C$13,{1,0 }+{-1,1}/(1+(B$31/$D$2:$D$13)^$A32),0)))-$C$2:$C$13
calculates the same quantity without this problem.

In addition to reversing the column order to substitute TRANSPOSE for
INDEX,
I had to add an extra column of zeros in the constants of this new formula
to
make the arrays conformable for MMULT. Apparently when you tell LINEST
that
you don't want an intercept, you get a zero intercept instead; though that
only becomes apparent when you embed LINEST in an array formula.

On further refection, the whole issue could have been avoided in this
case,
since
=INDEX(LINEST($C$2:$C$13,{1,0}+{-1,1}/(1+(B$31/$D$2:$D$13)^$A32),0,1),5,2)
also returns the value I wanted from =SUMSQ(formula) and as a bonus, does
not require array entry.

Jerry

I tinkered around with TRANSPOSE and still couldn't get it to work but I
left the INDEX call in.

I'm convinced there are some things about Excel that not a single person on
this planet understands!

--
Biff
Microsoft Excel MVP