Joe Smith wrote:
James Elliott - WA Rural Computers wrote:
When I saved a Calc spreadsheet in Excel format to send to my
Accountant, the SUMPRODUCT formulas did not work because a Calc
formula like this:
=SUMPRODUCT(A1:A3000; B1:B3000)
was converted to :
=SUMPRODUCT(A1:A3000, B1:B3000)
ie semicolons ";" replaced with commas ","
but Excel wanted it in this format:
=SUMPRODUCT((A1:A3000)*( B1:B3000))
...
...
As far as I know, SUMPRODUCT works the same way in both Excel and Calc,
and the syntax with comma-separated arguments is correct for Excel, so
I'm wondering how/why that would generate an error in Excel.
The alternative using an explicit multiplication is just another way to
do the same calculation, like writing SUM(3+3+3) instead of SUM(3;3;3),
so the difference must be something subtle.
Ok, 30 seconds of googling tells me that Excel users seem to write
SUMPRODUCT almost exclusively in the second form. However 30 minutes of
searching hasn't brought me any clear description of _why_ they do it.
As far as I can see, it's because everyone else writes it that way.
I suspect it has something to do with how the two forms treat
non-numeric items in the data. SUMPRODUCT(a,b) treats any non-numeric
values (e.g. text or boolean values) in a or b as zero, effectively
ignoring them in calculating the result.
The second form, SUMPRODUCT(a*b), will carry out the normal Excel
conversion of text to numbers and boolean tests to 0 or 1.
My guess is that someone started writing "a*b" just so that the
calculation would include any text or boolean values that Excel would
otherwise calculate with, and the practice just caught on so that people
write it that way whether it's necessary or not.
I also see lots of examples using SUMPRODUCT(--(a),--(b)), which is
another way to force a conversion to numeric value for each item in the
range.
I suppose, for best compatibility with Excel, the "a*b" form would be
the one to use. It shouldn't make any difference for straight numeric
data like your example, but for logical tests, the "a*b" will work in
both Calc and Excel, but "a,b" will only work in Calc.
<Joe
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
