Hi again Joe
In the smiple example I gave in my original post, i deliberatly kept it
simple to get my point accross. however, as you can see in this real life
formula:
=SUMPRODUCT((chqDEBIT)*(chqCODE=A13)*(chqDATE>=$D$4)*(chqDATE<=$D$5))
my formula does, indeed, contain boolean tests so you are probably correct
in what you sayin your second reply (see below).
So, in Calc (forget Excel) we can use either of these formats, can we?:
=SUMPRODUCT(A1:A3000; D1:D3000<=TEST)
or
=SUMPRODUCT((A1:A3000)*(D1:D3000<=TEST))
Am I correct?
Kind regards, James
----- Original Message -----
From: "Joe Smith" <[EMAIL PROTECTED]>
To: <users@openoffice.org>
Sent: Friday, September 19, 2008 11:54 PM
Subject: [users] Re: INTERESTING SUMPRODUCT OBSERVATION.?
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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
