Summing Rows in an array
Posted by Brian on August 02, 2001 1:11 PM
I have a 2 x 3 array that has the values {1,2,3 ; 4,5,6}, and I would like to end up with a 2 x 1 array with the values {6;15}. Is there a way to do this?
Thanks!
Brian
Posted by Cory on August 02, 2001 2:38 PM
Highlight D1:D2 then type an equals followed by:
highlight A1:A2, then +
highlight B1:B2, then +
highlight C1:C2, then (watch carefully!) press Cont + Shift + Enter in that order.
Did that work?
Cory
Posted by Brian on August 02, 2001 3:15 PM
Cory,
Thanks for your reply. That solution works well if the rows in question are cells I can highlight...unfortunately, the array is already the result of several data manipulations. Therefore, unless I add another step to my calculations, and place the array in a two dimensional range first, I think I need to use some kind of a formula.
Thanks,
Brian
Posted by Mark W. on August 03, 2001 6:34 AM
{=SUM({1,2,3;4,5,6}*{1;0})*{1;0}+SUM({1,2,3;4,5,6}*{0;1})*{0;1}}
Posted by Aladin Akyurek on August 03, 2001 12:45 PM
Mark,
Do you know how to make this computation generic?
BTW, no need for {} around your formula.
Aladin
Posted by Mark W. on August 03, 2001 1:57 PM
> Do you know how to make this computation generic?
Not quite sure what you mean, but I believe you
have to look at each case individually, breaking
arrays down into constituent parts, performing
the summary, and then re-assembling.
> ...no need for {} around your formula
Perhaps, no need if you're viewing results in
formula bar or passing to another function that
accepts an array. But necessary if you're
returning results to a 2-cell range; otherwise,
all you'll get is 6 instead of {6;16}.
Posted by Mark W. on August 03, 2001 2:05 PM
Oops!! Make that "{6;15}"
Posted by Aladin Akyurek on August 03, 2001 2:49 PM
Was afraid you were going to say so. I thought of setting up a lookup table for particular, frquent cases like 3 x 2, 3 x 3..., but that doesn't look like an attractive option.
An UDF could do this generically by taking the result of the Brian's formula that produces the array as one of its args and outpuuting an array whose dimensions are reduced by summing.
Yeah, it depends on how it's going to be used.
