Home » File Formats » Spreadsheet_Excel_Writer » Bug #3171

[2005-01-12 02:13 UTC] pear at baskettcase dot com

Description:
------------
Error with this formula:

=SUMPRODUCT((MOD(COLUMN(C6:CK6)-CELL("Col",C6),3)=0)*(C6:CK6))

I get this error:

')' token expected

Although the above formula works just fine in excel itself... so something weird is going on...

[2005-11-09 01:39 UTC] xnoguer at php dot net

that doesn't seem a valid formula (openoffice doesn't accept it). What are you trying to calculate? (in english)

[2005-11-09 01:54 UTC] pear at baskettcase dot com

What it is doing is basically taking every 3rd column and
adding it up to a totals column for 3 different options.

I hope that makes sense :)

[2005-11-11 15:03 UTC] xnoguer at php dot net

shouldn't that be :
=SUMPRODUCT(IF(MOD(COLUMN(C6:CK6)-CELL("Col",C6),3)=0,1,0)*SUM(C6:CK6))

then ? the only difference is that I'm using an explicit IF.

[2005-11-11 18:13 UTC] pear at baskettcase dot com

That works for that particular cell, but then the cell right
next to it that totals up the next section of every third cell
in that row does not work.. and I have no idea how it comes up
with the number it did. Also have you tried your formula in
this class? My guess is that it will fail like the one that I
am using. Plus the one that I am using works in Excel, but
just does not work in this class.

[2005-11-12 04:38 UTC] xnoguer at php dot net

I tried the formula and oo doesn't complain on it. Do you have a data set so I can test it?

[2005-11-14 19:35 UTC] pear at baskettcase dot com

Ok I am going to send you what is created currently. You will
see some formulas that you have to actually physically put in
the = sign to have it execute the formula since it either
crashes Excel for the PC, but not the Mac or for example in
the timesheets, not the totals sheet, at the end is the
formula that we've been discussing, which this
spreadsheet_excel writer class chokes on.