Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Another Array Mystery
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Another Array Mystery
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 __ 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Another Array Mystery
"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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Another Array Mystery
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Mystery | Excel Discussion (Misc queries) | |||
Macro Mystery | Excel Discussion (Misc queries) | |||
Mystery Value in my Spreadsheet! | Excel Discussion (Misc queries) | |||
One of lifes little mystery? | Excel Worksheet Functions | |||
MYSTERY!! | Excel Discussion (Misc queries) |