Another Array Mystery

[Jerry W. Lewis]

I have an array formula, that returns a 12x1 array of numbers. If I return
that array to a range, then =COUNT(range) returns 12, but =COUNT(formula)
returns zero, while =COUNTA(formula) returns 12. All formulas [with the
obvious exception of =COUNT(range)] are array entered. What gives?

Jerry

The formula is
MMULT({1,0}+{-1,1}/(1+(B$31/$D$2:$D$13)^$A32),INDEX(LINEST($C$2:$C$13,{1,0}+{-1,1}/(1+(B$31/$D$2:$D$13)^$A32),0),1,{2;1}))-$C$2:$C$13

 

[T. Valko]

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 so COUNT = 0

COUNTA will count those errors so COUNTA = 12

If you enter COUNT(formula) in the 12x1 array then it returns 12 as expected
*but* it's not counting 12 numeric results from MMULT(...)-$C$2:$C$13. It's
counting the array of COUNT(#VALUE!) = 0 12 times.

 

[Héctor Miguel]

hi, Jerry !

- while counta(... includes any non-empty cells, count(... *can only* count numbers
- when the mmult(... formula is written in an array-range, the numbers "shows-up" (but...)
- while in a single cell (within the array-range) you press {F2}+{F9}, evaluation shows only "error-values" (so...)

one way to use count(... (in a CSE formula) to count "numbers" (while a single cell-formula shows errors),
could it be: COUNT(--ISERROR(formula))

hth,
hector.

__ OP __

 

[Jerry W. Lewis]

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

 

[T. Valko]

...

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!