Hi Joe
I tried replacing the explicit multipliers with commas in this formula:
=SUMPRODUCT((chqDEBIT)*(chqCODE=A13)*(chqDATE>=$D$4)*(chqDATE<=$D$5))
ie. I made it read:
=SUMPRODUCT(chqDEBIT,chqCODE=A13,chqDATE>=$D$4,chqDATE<=$D$5)
and the formula no longer worked! The sumproduct total I had in that cell
just disappeared - no error message, just no result.
Kind regards, James
----- Original Message -----
From: "Joe Smith" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Friday, September 19, 2008 11:09 AM
Subject: [users] Re: INTERESTING SUMPRODUCT OBSERVATION.?
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))
...
How did it "not work" in Excel? Did you get an error; if so, what was it?
Did you get the wrong answer, or something else?
What suggested to you that Excel wanted the other syntax?
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.
<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]
