Apache OpenOffice (AOO) Bugzilla – Issue 74625
Calc: Transposition: Formula Error
Last modified: 2017-05-20 11:11:38 UTC
This deals with an ERROR in the *automatic Formula Update* when transposing a selection in Calc. 1. ERROR in Referenced Cells: wrong formula update 2. VLOOKUP and HLOOKUP are NOT updated 1. Wrong Formula Update: ==================== This happens when pasting a transposed selection containing formulas over the same location. a. STEPS TO REPRODUCE: ================== 1. create a spreadsheet: A2: = 1 ; B2: = B2 * 2 A3: = 2 ; B3: = B3 * 2 ... A6: = 5 ; B6: = B6 * 2 2. Select A2:B6 3. CUT the selection NOTE: the selection is cut away, so NOTHING is displayed in A2:B6, giving the false impression that PASTing here is unproblematic 4. CLICK on A2 5. PASTE special; check the 'TRANSPOSE' checkbox 6. new data is in A2:E3 7. the cell A3 displays an ERROR: '#REF!' The content in this cell is "=#REF!2*2". It should have been "=A2 * 2". All other formulas in the other cells are updated correctly. I do NOT know how exactly the formula update mechanism operates, BUT this should be pretty simple and could be viewed as an affine transform. b. CORRECT FORMULA UPDATE ====================== Lets say the initial data is in [CbRb; CeRe], where C stands for "Column" and R for "Row". b = begin (first element); e = end (last element); Transposing this selection and pasting it to C'[0]R'[0] will yield this: the new data matrix will be: " C'[0]R'[0]" - first cell "(C'[0]+Ce-Cb)(R'[0]+Re-Rb)" - last cell, where C'[0] is the number of the (first) column and R'[0] is the number of the (first) row where the data is pasted; For an arbitrary initial cell (Ci and Rj), the new values will be: C'[i] = C'[0] + R[j] - Rb R'[j] = R'[0] + C[i] - Cb c. SPECIAL CONDITIONS ================== IF one cell contains a formula referencing a selection/ matrix that OVERLAPS the transposed selection, BUT extends beyond its boundaries, then an ERROR should be generated. The transposition would split the initial matrix into a number of NON-contiguous matrices and the user probably did NOT want this. In pseudocode, this would look like: [CbRb, CeRe] - initial matrix before transposition [CiRi, CjRj] - referenced matrix inside the formula code to update formula and detect error: IF((Ci <= Ce) && (Cj >= Cb) && (Ri <= Re) && (Rj >= Rb)) { // THIS MATRIX CONTAINS ELEMENTS FROM THE MATRIX // THAT WILL BE TRANSPOSED, SO IT MUST BE UPDATED IF((Ci < Cb) || (Cj > Ce) || (Ri < Rb) || (Rj > Re)) { // THE MATRIX EXTENDS BEYOND BOUNDARIES // OF TRANSPOSED MATRIX => ERROR // THE INITIAL MATRIX WOULD BE SPLIT INTO // AT LEAST 2 NON-CONTIGUOUS MATRICES SET_ERROR("MATRIX OVERLAP"); } ELSE { // UPDATE FORMULA C'i = C'[0] + Ri - Rb // FIRST ELEMENT R'i = R'[0] + Ci - Cb C'j = C'[0] + Rj - Rb // LAST ELEMENT R'j = R'[0] + Cj - Cb _SPECIAL_UPDATE(); // SEE POINT 2: LOOKUP } } 2. UPDATING LOOKUP =============== When transposing a matrix, any LOOKUP function that references that matrix should be updated. VLOOKUP() should become HLOOKUP(), and HLOOKUP() should become VLOOKUP() [only IF 2nd argument references the transposed matrix] In pseudocode, this would look like: function _SPECIAL_UPDATE() { IF(_IS_ARGUMENT_OF_FUNCTION) { IF(_IS_FUNCTION(VLOOKUP) && _IS_2nd_ARG) { function = HLOOKUP; } ELSE IF(_IS_FUNCTION(HLOOKUP) && _IS_2nd_ARG) { function = VLOOKUP; } } }
I introduced an error in the *STEPS TO REPRODUCE* a. STEPS TO REPRODUCE: ================== 1. create a spreadsheet: A2: = 1 ; B2: = A2 * 2 A3: = 2 ; B3: = A3 * 2 ... A6: = 5 ; B6: = A6 * 2 write in B[i] = A[i] * 2 , NOT B[i] * 2
discoleo, any chance you could attach sample file? Thanks.
Created attachment 43134 [details] Test Sheet Demonstrating ERROR
I have attached a sample Test-Sheet that demonstrates the ERROR. The first sheet called "Description" has the data before the copy/transpose/past operation. Just select the "A2:B6" range as described on the sheet, CUT and "Paste special" using transpose inside the cell A2. The results will look as in the 2nd sheet ("Results"), where in the cell A3 (former B2) is an ERROR. A3: (former B2): instead of the formula "=A2 * 2", the updated formula looks like "=#REF!2 * 2"!!!
Confirming with 2.2m7 on WinXP - formula in top right cell is not converted properly.
Hi Eike, seems to be yours. Frank
Accepted for the reference error. Note that this happens only if the first column was included in the cut operation and pasting to the first column. No error if the original block started at a different column or was pasted to a different column. Regarding the "c. special conditions": rejected. We never generate an error when a portion of a referenced range is moved (cut&pasted) to some other place, transposing doesn't change there anything. Regarding the HLOOKUP/VLOOKUP exchange: rejected. I doubt users seriously expect the formulas' function names to change just because they moved some data around. There's also no indication whether the change would actually be correct and intended.
1. Well, it is good to know that the work has started. Though, I cannot agree, that when moving data around, there should be NO warning IF something messy will happen. Maybe ERROR is to much, BUT at least a warning message that some formulas could get broken. Calc does NOT do really nasty things (as breaking the matrix into non-contiguous matrices), BUT the formulas are still broken. I will attach a *Test Sheet* demonstrating this behavior. Perform the steps as described in the sheet 'Initial'. After Cut/Transpose/Paste, the formula inside A19 is NOT updated. This is BETTER so, BUT the user has NO clue that this *formula is probably wrong.* 2. LOOKUP ====== Well, this is another problem. Lets say we have the following formula: = VLOOKUP('a_cell_NOT_really_important'; A1:B10; 2) and we cut/transpose/paste A1:B10 (pasting into A1 for convenience, BUT because of previous error I used a different cell inside the test sheet); The new range becomes A1:J2. The problem is, VLOOKUP() searched inside A1:A10 for first value, and NOW it searches inside A1:A2, because it is still VLOOKUP(). Something very wrong has happened. It should search inside A1:J1, BUT this is a horizontal search AND NOT a vertical one, so HLOOKUP() should be used. So, while Calc updates the formula, this becomes a total MESS. LOOKUP() functions won't be able to search anymore, BECAUSE the range was TRANSPOSED, BUT the range is NOT suited for the corresponding LOOKUP. By virtue of the *transposition*, what was vertical becomes horizontal AND what was horizontal becomes vertical. This error applies ALWAYS (always reproducible) when the transposed range is the 2nd argument of the LOOKUP function! So, my belief is that a correct formula update should update the function, too.
Created attachment 43170 [details] Test Case for 2nd Error and LOOKUP errors
> Well, it is good to know that the work has started. Note that in IssueTracker terms STARTED means ASSIGNED/ACCEPTED. Yes, bad wording, blame CollabNet. > Though, I cannot agree, that when moving data around, there should be > NO warning IF something messy will happen. Well, currently there is none, and it doesn't make sense to request introducing it with this issue, which is about a particular error with the transpose functionality. If you think that a general "pop up warning if a formula touches a cell range that is moved" is needed, feel free to file a separate request for enhancement issue, though I doubt it would get a high priority. > 2. LOOKUP If we started to adapt HLOOKUP/VLOOKUP there were also COLUMNS/ROWS and others. For Add-Ins or user-defined function we wouldn't even know if and how to handle it. Again, you may file an RFE for it and see how the User Experience team will like it. Anyway, as said before, I accepted this issue for the reference error of the case that transposing the range starts in the first column. Please don't clutter up with additional topics. Thanks.
change target from 2.x to 3.x according to http://wiki.services.openoffice.org/wiki/Target_3x
Reset assigne to the default "issues@openoffice.apache.org".