SUMPRODUCT - comma versus semicolon

[Epinn]

A1:E1 3 5 6 3 2

=SUMPRODUCT((A1:E1)*{1;2;3;4;5}) returns 285
=SUMPRODUCT((A1:E1)*{1,2,3,4,5}) returns 53

I use evaluate formula and I know what the above formulae do.

I am familiar with commas but semicolons are new to me. Anyone knows of any documentation on SUMPRODUCT and semicolon? Don't think I saw semicolon in Bob's paper.

By the way, SUM+CSE will return the same results respectively.

Please feel free to comment.

Thanks.

Epinn

 

[JE McGimpsey]

From XL Help ("About array formulas and array constants"):

Separate values in separate columns with commas (,) and values in
separate rows with semicolons (. For example, to represent the
values 10, 20, 30, and 40 in four columns, enter {10,20,30,40}; to
represent the same values in four rows, enter {10;20;30;40}. For
2-by-4 array constant (two rows by four columns), you would enter
{10,20,30,40;50,60,70,80}.

So your first formula multiplies each element of A1:E1 by each element
of the one-column array (then adds), while the second formula multiplies
each element of A1:E1 against only its corresponding element in the
one-row array, then adds.

 

[Bob Phillips]

I didn't include in the paper as it is not a SP matter particularly, more to
do with how arrays are handled. But it is interesting, and I might try to
include something about it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

A1:E1 3 5 6 3 2

=SUMPRODUCT((A1:E1)*{1;2;3;4;5}) returns 285
=SUMPRODUCT((A1:E1)*{1,2,3,4,5}) returns 53

I use evaluate formula and I know what the above formulae do.

I am familiar with commas but semicolons are new to me. Anyone knows of any
documentation on SUMPRODUCT and semicolon? Don't think I saw semicolon in
Bob's paper.

By the way, SUM+CSE will return the same results respectively.

Please feel free to comment.

Thanks.

Epinn

 

[Epinn]

Bob and Roger,

I think you know me by now. Remember how I thought Boolean was for SUMPRODUCT only? This is no different; I thought this comma/semicolon issue is SUMPRODUCT related. This is a case of history repeating and SUMPRODUCT addiction. Please feel free to laugh; consider this your joke for the day.

My memory is coming back. Thank you, Mr. McGimpsey for your help. I have seen formulae using comma and semicolon before. I was thrown off by the fact that there was only one number and then a semicolon. You know I am used to seeing 1,2;3,4;5,6 ...... something like that.

I have to refresh myself some more and I won't close this thread yet.

Have you read my discovery of today? Under the thread "Interpreting comma .....," I talked about =IF(A1,) with A1 containing all sorts of values. Lately, I seem to have some affinity with comma. <G>

Epinn

I didn't include in the paper as it is not a SP matter particularly, more to
do with how arrays are handled. But it is interesting, and I might try to
include something about it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

A1:E1 3 5 6 3 2

=SUMPRODUCT((A1:E1)*{1;2;3;4;5}) returns 285
=SUMPRODUCT((A1:E1)*{1,2,3,4,5}) returns 53

I use evaluate formula and I know what the above formulae do.

I am familiar with commas but semicolons are new to me. Anyone knows of any
documentation on SUMPRODUCT and semicolon? Don't think I saw semicolon in
Bob's paper.

By the way, SUM+CSE will return the same results respectively.

Please feel free to comment.

Thanks.

Epinn

 

[Bob Phillips]

Which group is that in?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob and Roger,

I think you know me by now. Remember how I thought Boolean was for
SUMPRODUCT only? This is no different; I thought this comma/semicolon issue
is SUMPRODUCT related. This is a case of history repeating and SUMPRODUCT
addiction. Please feel free to laugh; consider this your joke for the day.

My memory is coming back. Thank you, Mr. McGimpsey for your help. I have
seen formulae using comma and semicolon before. I was thrown off by the
fact that there was only one number and then a semicolon. You know I am
used to seeing 1,2;3,4;5,6 ...... something like that.

I have to refresh myself some more and I won't close this thread yet.

Have you read my discovery of today? Under the thread "Interpreting comma
......," I talked about =IF(A1,) with A1 containing all sorts of values.
Lately, I seem to have some affinity with comma. <G>

Epinn

I didn't include in the paper as it is not a SP matter particularly, more to
do with how arrays are handled. But it is interesting, and I might try to
include something about it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

A1:E1 3 5 6 3 2

=SUMPRODUCT((A1:E1)*{1;2;3;4;5}) returns 285
=SUMPRODUCT((A1:E1)*{1,2,3,4,5}) returns 53

I use evaluate formula and I know what the above formulae do.

I am familiar with commas but semicolons are new to me. Anyone knows of any
documentation on SUMPRODUCT and semicolon? Don't think I saw semicolon in
Bob's paper.

By the way, SUM+CSE will return the same results respectively.

Please feel free to comment.

Thanks.

Epinn

 

[Epinn]

http://groups.google.ca/group/micro...daecd?lnk=st&q=&rnum=1&hl=en#f6b09d198dfdaecd
or
http://tinyurl.com/yyanyg

Third post on my discovery of =IF(A1,)

I find INDEX and comma dialogue with JMB interesting as well. This is what led me to IF and comma.

Thanks for your attention.

Epinn

Which group is that in?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob and Roger,

I think you know me by now. Remember how I thought Boolean was for
SUMPRODUCT only? This is no different; I thought this comma/semicolon issue
is SUMPRODUCT related. This is a case of history repeating and SUMPRODUCT
addiction. Please feel free to laugh; consider this your joke for the day.

My memory is coming back. Thank you, Mr. McGimpsey for your help. I have
seen formulae using comma and semicolon before. I was thrown off by the
fact that there was only one number and then a semicolon. You know I am
used to seeing 1,2;3,4;5,6 ...... something like that.

I have to refresh myself some more and I won't close this thread yet.

Have you read my discovery of today? Under the thread "Interpreting comma
......," I talked about =IF(A1,) with A1 containing all sorts of values.
Lately, I seem to have some affinity with comma. <G>

Epinn

I didn't include in the paper as it is not a SP matter particularly, more to
do with how arrays are handled. But it is interesting, and I might try to
include something about it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

A1:E1 3 5 6 3 2

=SUMPRODUCT((A1:E1)*{1;2;3;4;5}) returns 285
=SUMPRODUCT((A1:E1)*{1,2,3,4,5}) returns 53

I use evaluate formula and I know what the above formulae do.

I am familiar with commas but semicolons are new to me. Anyone knows of any
documentation on SUMPRODUCT and semicolon? Don't think I saw semicolon in
Bob's paper.

By the way, SUM+CSE will return the same results respectively.

Please feel free to comment.

Thanks.

Epinn

 

[Epinn]

Thank you Mr. McGimpsey for pointing me to the right direction. I was looking at the wrong places, namely, SUMPRODUCT.

Epinn

From XL Help ("About array formulas and array constants"):

Separate values in separate columns with commas (,) and values in
separate rows with semicolons (. For example, to represent the
values 10, 20, 30, and 40 in four columns, enter {10,20,30,40}; to
represent the same values in four rows, enter {10;20;30;40}. For
2-by-4 array constant (two rows by four columns), you would enter
{10,20,30,40;50,60,70,80}.

So your first formula multiplies each element of A1:E1 by each element
of the one-column array (then adds), while the second formula multiplies
each element of A1:E1 against only its corresponding element in the
one-row array, then adds.