Another Array Mystery
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.
--
Biff
Microsoft Excel MVP
"Jerry W. Lewis" wrote in message
...
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
|