In a previous thread I said that my SUMPRODUCT formula in Calc was not
converting to something that worked in Excel when I saved my Calc
spreadsheet in Excel format to give to my accountant.
There were several replies, thank you, but none which really helped.
However, by doing an online Excel tutorial I found that the problem was that
when saving an OOo-Calc spreadsheet in Excel format, the syntax is not
correctly converted.
Here is an example of my original Calc formula:
=SUMPRODUCT(chqDEBIT; chqCODE="b"; chqDATE>=$D$4; chqDATE<=$D$5)
Here is what it converts to when saved as 'Excel':
=SUMPRODUCT(chqDEBIT, chqCODE="b", chqDATE>=$D$4, chqDATE<=$D$5)
(ie semicolons are simply replaced with commas)
Here is the edited Excel formula which works:
=SUMPRODUCT((chqDEBIT)*(chqCODE="b")*(chqDATE>=$D$4)*(chqDATE<=$D$5))
ie the calc semicolons are replaced with asterisks or multiply symbols (*)
and brackets are used where appropriate. The syntax converter cannot cope
with this because of the many variations possible in this type of formula)
In all fairness, when I saved my Calc spreadsheet in Excel format, just now,
there was a warning pop-up which said that some formulas might not copy
across properly.
I hope this is helpful to some others on the list.
Kind regards, James
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
