Equation to sum up the cell values of only even (or odd) columns in a row.

Hello

Since I don't use Numbers v3, I cannot be of much help about it. But it seems to me that Numbers v3.2.2 has bug in function parser which throws the said error incorrectly when parsing SUMPRODUCT(<range>, <constant array>).

If I guess right, you might try tricking the parser by using TRANSPOSE() on arguments redundantly. Something like the below.

Table 1 (in English, decimal-period locale) A1 n1 A2 =RANDBETWEEN(0,10) A3 =RANDBETWEEN(0,10) A4 =RANDBETWEEN(0,10) A5 =RANDBETWEEN(0,10) B1 n2 B2 =RANDBETWEEN(0,10) B3 =RANDBETWEEN(0,10) B4 =RANDBETWEEN(0,10) B5 =RANDBETWEEN(0,10) C1 n3 C2 =RANDBETWEEN(0,10) C3 =RANDBETWEEN(0,10) C4 =RANDBETWEEN(0,10) C5 =RANDBETWEEN(0,10) D1 n4 D2 =RANDBETWEEN(0,10) D3 =RANDBETWEEN(0,10) D4 =RANDBETWEEN(0,10) D5 =RANDBETWEEN(0,10) E1 n5 E2 =RANDBETWEEN(0,10) E3 =RANDBETWEEN(0,10) E4 =RANDBETWEEN(0,10) E5 =RANDBETWEEN(0,10) F1 n6 F2 =RANDBETWEEN(0,10) F3 =RANDBETWEEN(0,10) F4 =RANDBETWEEN(0,10) F5 =RANDBETWEEN(0,10) G1 G2 G3 G4 G5 H1 odd H2 =SUMPRODUCT($A2:$F2,TRANSPOSE(TRANSPOSE({1,0,1,0,1,0}))) H3 =SUMPRODUCT($A3:$F3,TRANSPOSE(TRANSPOSE({1,0,1,0,1,0}))) H4 =SUMPRODUCT($A4:$F4,TRANSPOSE(TRANSPOSE({1,0,1,0,1,0}))) H5 =SUMPRODUCT($A5:$F5,TRANSPOSE(TRANSPOSE({1,0,1,0,1,0}))) I1 even I2 =SUMPRODUCT(TRANSPOSE($A2:$F2),TRANSPOSE({0,1,0,1,0,1})) I3 =SUMPRODUCT(TRANSPOSE($A3:$F3),TRANSPOSE({0,1,0,1,0,1})) I4 =SUMPRODUCT(TRANSPOSE($A4:$F4),TRANSPOSE({0,1,0,1,0,1})) I5 =SUMPRODUCT(TRANSPOSE($A5:$F5),TRANSPOSE({0,1,0,1,0,1}))

Notes.

I used slightly different formulae in H and I just to show the possibilities.

Please change the argument delimiter from comma to semi-colon according to locale settings.

Table is built with Numbers v2. Hope it behaves with Numbers v3.2.2 as well...

Good luck,

H

Hiroto wrote:

But it seems to me that Numbers v3.2.2 has bug in function parser which throws the said error incorrectly when parsing SUMPRODUCT(<range>, <constant array>).

Hi Hiroto,

Any function parser bug in Numbers 3.5.3 would appear to be only for localities where ; is used instead of , in formulas. I can't reproduce the problem here.

SG

Hello SG,

Yes, it would be locale-dependent bug. But I also suspect bug would be only in Numbers v3 prior to v3.5, locale-dependent or not.

H

Another thought.

It might be that Numbers v3.2.2 is wrongly interpretting {1,0,1,0,1,0} as column vector instead of row vector, which would explain why SUMPRODUCT({1,2},{3,4}) works whilst SUMPRODUCT(B3:C3,{3,4}) falis due to array length mismatch.

If it is the case, you might transpose constant array before passing it to SUMPRODUCT() such as:

=SUMPRODUCT($A2:$F2,TRANSPOSE({1,0,1,0,1,0}))

instead of:

=SUMPRODUCT($A2:$F2,{1,0,1,0,1,0})

---

Also, you might temporarily set decimal separator to period in System Preferences and restart Numbers so that you set Numbers' arguments delimiter to comma instead of semicolon and test the formulae. If it works as expected, we may conclude bug is specifically in decimal-comma (semicolon-arguments-delimiter) localisation code in Numbers.

Regards,

H

Hi H,

With decimal separator set to , I can reproduce the "must be same length error" on my machine.

What does work is this (in v 3.5.3):

=SUMPRODUCT(B3:C3;TRANSPOSE({3;4}))

And this also works:

=SUMPRODUCT(B1:B2;{3;4})

Does this suggest that there is a bug in the parser? Or is this "normal" behavior when the decimal separator is set to , ?

SG

Hiroto deserves the credit. It will be interesting to have his comments on the "is this a bug?" question, and, if so, what should the behavior be when , is the decimal separator. If it does turn out to be a parser bug, we could report it via Numbers > Provide Numbers Feedback in the menu.

SG

Hello SG,

I'd consider that this is locale-dependent bug in function parser or calculation engine of Numbers v3 because localisation settings on number format must not affect the semantics of array formula.

A formula in decimal-period locale:

=SUMPRODUCT(B3:C3,{3,4})

and its corresponding formula in decimal-comma locale:

=SUMPRODUCT(B3:C3;{3;4})

must bahave the same and yield the same result except for localisation. If {3,4} is interpreted as row vector, {3;4} mush be interpreted as row vector as well.

You might report this as a bug.

All the best,

Hiroto

Hello Dylan Cromwell,

You're quite welcome! Glad to be of help. 🙂

Just please note that the proposed workaround method using TRANSPOSE() on constant array in decimal-comma locale is only for decimal-comma locale and must not be applied in decimal-period locale.

I'd consider this issue is caused by locale-dependent bug of Numbers v3 and thus should the bug be fixed someday, you'd need to undo the workaround in the rectified version.

Kind regards,

Hiroto

Hi Hi,

Before reporting a bug, I decided to do a little empirical work, with Excel 2011. The results were unexpected!

With the decimal separator set to , this is the only thing that works, other than using =TRANSPOSE({3;4}).

=SUMPRODUCT(A3:B3;{3\4})

Have you ever seen the \ used that way? (It doesn't work in Numbers 3.5.3).

Maybe the Excel 2011 parser has problems too.

SG

Hello SG,

Sorry for late reply. I myself don't use Excel 2011 and cannot comment on it but as far as I can tell with LibreOffice Calc 4.3.7 under OS X 10.6.8, it lets me specify separators for function, array column and array row individually.

E.g., in default locale setting for Engish,

separator for function = , separator for array column = , separator for array row = ;

in default locale setting for German,

separator for function = ; separator for array column = . separator for array row = ;

For instance, {1,0,0;0,1,0;0,0,1} denotes 3 × 3 unit matrix in LibreOffice Calc in default locale setting for English.

LibreOffice Calc and Microsoft Excel support constant 2d-array which complicates situation. Possibly it might be that Numbers v3 has some hidden code to translate constant M × 1 matrix using ; for array row separator into corresponding column vector in Numbers (for importing purpose) and this translation somehow takes place even in its own function parser if and only if in decimal-comma locale, which is inconsistent and inappropriate behaviour to be considered bug.

And I'm not so surprised to see someone has chosen \ (instead of .) for array column separator when function separator is ; in Excel. Just wonder if there's no preferences setting to specify separators for array column and array row in any given locale in Excel?

Just my observations,

H

Hi H,

Thanks for the explanation. I submitted a bug report to Apple via Numbers > Provide Numbers Feedback referring to this thread.

SG