To comment on the following update, log in, then open the issue:
http://www.openoffice.org/issues/show_bug.cgi?id=85322
Issue #|85322
Summary|Inserting cols mis-updates range arg of functions
Component|Spreadsheet
Version|OOo 2.3.1
Platform|PC
URL|
OS/Version|Windows XP
Status|UNCONFIRMED
Status whiteboard|
Keywords|
Resolution|
Issue type|DEFECT
Priority|P3
Subcomponent|editing
Assigned to|spreadsheet
Reported by|undecidable
------- Additional comments from [EMAIL PROTECTED] Thu Jan 17 09:42:46 +0000
2008 -------
Selecting columns then inserting columns to the left of a range of columns
causes function ranges to be updated incorrectly
where the range argument of the function includes the selected columns
(explained in English below).
What appears to be happening is average(B2:E2) is being internally converted to
Average($B2:E2)
when columns are added and the column with the function in it is selected as
part of the range.
I use Average() as an example
though it appears to happen with all functions with range arguments.
I give an example of both bad behavior and much worse behavior.
Both happen in OOO 2.3.1 under WXP
both happen in OOO 2.1 under W2000.
Do not happen in OOO 2.2 under (K)ubuntu !
EXAMPLE 1 (bad)
To Reproduce
1a enter 1,2,3,4 in cells B2, C2, D2, E2
b enter formula in cell B3
=average(B2:E2)
c select cols B to E then insert columns
(this inserts 4 cols to the left of the range)
d the average formula, which should read:
=average(F2:I2)
now in error reads
=average(B2:I2)
2. now select the new col C and insert column.
the average formula still reads:
=average(B2:I2)
ie totally ridiculous.
Notes:
1 this is not the same as adding cols within the range,
where they should be included in the new range definition.
here we are adding cols to the left of a range.
2 Other functions exhibit the same behavior when their argument includes a
range, eg sum() though I have not verified all functions.
3 The same happens even if you try to just add 1 col not 4 as in my example.
(ie just select col B in step c above)
4 The same happens if your range covers rows rather than cols, and you insert
rows.
5 Excel behaves correctly in all cases.
EXAMPLE 2 (worse)
enter 1,2,3,4 in cells D2, E2, F2, G2
enter formula in cell D3
=average(D2:G2)
select cols B to G and insert columns
(this inserts 6 cols to the left of the range)
the average formula, which should now read:
=average(J2:M2)
now in error reads
=average(D2:M2)
Same issue as above,
just more surprising that it happens even when the start of the range
is two cells to the left of the cell containing the formula.
Why is it a Problem
Every year when financial institutions add 12 more months, they have to fix
formulas.
---------------------------------------------------------------------
Please do not reply to this automatically generated notification from
Issue Tracker. Please log onto the website and enter your comments.
http://qa.openoffice.org/issue_handling/project_issues.html#notification
---------------------------------------------------------------------
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]
