https://bugs.freedesktop.org/show_bug.cgi?id=62279
--- Comment #8 from Colin <[email protected]> --- Hi there again, Jacques, Sorry if this is becoming verbose, I have now had the opportunity to perform a more systematic test. It is questionable as to whether it is a genuine bug or a simple but fairly significant inconsistency in the manner in which cells can be identified for the SUM and Sigma functions. There appear to be three methods. Firstly, delete the contents of cells B11, B18 and B21 from the sample spreadsheet. 1. Cursor in B11 > Sigma -- result = selection marquee for SUM of B2:B10 (the selection marquee may be re-positioned and re-sized according to requirements) > CR to accept the formula and parameters -- result = SUM of B2:B10 automatically placed in B11 which is the first available empty cell. alternatively (clear B11) 2. Cursor in cell B1 > left mouse and hold, selecting range B2:B10(orB2:B11) > Sigma -- result = SUM of B2:B10 automatically placed in B11 which is the first available empty cell. alternatively (clear B11) 3. Cursor in cell B11 > type =SUM( > manually select cells B2:B10 > CR to accept the formula and parameters -- result = SUM of B2:B10 automatically placed in B11 which is the defined target location All reasonably logical Now try 4. Cursor in B18 > Sigma -- result = selection marquee for SUM of B13:B17 (the selection marquee may be re-positioned and re-sized according to requirements) > CR to accept the formula and parameters -- result = SUM of B13:B17 automatically placed in B18 which is the first available empty cell. alternatively (clear B18) 5. As 4 above but re-position and re-size the selection marquee to include cells B11:B17 -- result = cumulative total of B11:B17 in cell B18. alternatively (clear B18) 6. Cursor in cell B11 > left mouse and hold, selecting range B11:B17(orB11:B18) > Sigma -- result = SUM of B13:B17 automatically placed in B18 which is the first available empty cell but NOT The Required (SUM(B11:B17)) Calculation. The formula can be edited to provide the desired result. Now try 7. Cursor in B21 > Sigma -- result = selection marquee for SUM of B18:B18 (the selection marquee may be re-positioned and re-sized according to requirements) > CR to accept the formula and parameters -- result = SUM of B18:B18 automatically placed in B21 which is the target cell. alternatively (clear B21 but leave existing formula in B11) 8.Cursor in cell B11 > left mouse and hold, selecting range B11:B21 > Sigma -- result = SUM of B19:B20 automatically placed in B21 which is the first available empty cell but, ironically, the SUM is 0 all "real data" in the range B13:B17 and the preexisting sub totals in B11 & B18 have been completely ignored. Definitely NOT The Required Calculation. The formula can be edited to provide the desired result. It could be argued that it is illogical to include the real numbers and their sub total in a later total but that should flag an error condition rather than just ignoring all data elements and providing the false result of 0. For the record clear B21 Cursor in B21 > type =sum( > select the range B10:B18 > CR -- result = mathematically correct but a totally illogical use of the data. We cannot program user data abuse out of the product but we should not leave illogical operation of valid requests in it either As demonstrated, the results of a manual selection of a range for Sigma are not only handled incorrect but also inconsistently, depending on just how many Sigma'd cells are included in the selection. I have not attempted to test for a greater number of data blocks with sub totals but could undertake that fairly easily if it becomes a requirement. It could be that further input from the original analyst may be necessary to ascertain just what they think the procedures should be, or, we can cheat a little and see what happens in Excel :). Best regards, Colin -- You are receiving this mail because: You are the assignee for the bug.
_______________________________________________ Libreoffice-bugs mailing list [email protected] http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
