Ask Your Question

Formulas proced with drag handle does not display correct values. [closed]

asked 2014-03-08 06:54:45 +0100

andrewH gravatar image

updated 2015-09-10 01:32:51 +0100

Alex Kemp gravatar image

Oakland Emp & Unemp.odsDear Folks-- I have a Calc worksheet with simple columns of numbers representing employment and unemployment numbers and rates. These numbers were copied as individual columns from Excel spreadsheets downloaded from the U.S. Bureau of Labor Statistics. They did not bring any formatting or formulas with them that I can identify by looking in the cells - just numbers. I am trying to take the difference of several pairs of these columns. I write out the difference in a cell in the usual way, e.g. "=M12-G12" (without the quotation marks) at the top of a column. I then grab the cell handle and pull it down to the bottom of the column. This fills the column with copies of the formula that differ in having the current row number in place of the original row number from the first entry in the column, as I expected. However, all of these formulas evaluate to the same number - the number in the original cell. This number is incorrect for all of the numbers but the original, and does not match the result of calculating the formula in the cell.

I have tried the following things which do not work: Saving the file, closing it, and reopening it; Cutting the column and pasting it back in the same place; Cutting the column and pasting it back in the same place as formulas only; Cutting one of the copied columns and pasting it back as numbers only; Dragging from the bottom instead of the top; Dragging to the right or left instead of vertically (again the formulas are correct but the number remains the value of the original cell); Cutting the entire spreadsheet and pasting it into a different sheet; Dragging the handle while holding down the Ctrl or Shift key; Entering the formula in a different column; Summing the numbers in two of these results columns sums the numbers displayed, not the correct results from the formula; Placing the cursor at the end of the formula in the cell and hitting return; Placing the cursor at the end of the formula in the formula bar window, or whatever it is called, and hitting return.

If I place the cursor in the formula bar, make some change in the formula, such as deleting the final "2" in the example I provided above, and then type the 2 back in ... (more)

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 08:25:03.574336


Nope, karma is still 1. This sort of discourages new posters from putting up information that may be necessary to allow others to answer their question, no?

andrewH gravatar imageandrewH ( 2014-03-08 06:56:59 +0100 )edit

You should have a enough karma now.

ROSt52 gravatar imageROSt52 ( 2014-03-08 10:32:52 +0100 )edit

I attached the file using the dialog box, and it rather oddly put the link at the very beginning of my post. See above.

andrewH gravatar imageandrewH ( 2014-03-22 01:57:20 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2014-03-08 16:06:30 +0100

m.a.riosv gravatar image

Hi @andrewH, verify if they were pasted as text (left align) not as number, this can happen if your locale has comma as decimal separator while it is a point in US. If so, use Menu/Data/TexttoColumns to convert in numbers, column by column.

edit flag offensive delete link more


Unfortunately I do not remember if I right-justified the text after pasting, but there are no quotation marks in the cell, and the Numbers tab in the Format cells dialog box seems to claim that they are of format general. My Locale setting is Default - English (USA)

andrewH gravatar imageandrewH ( 2014-03-22 00:53:59 +0100 )edit

Please verify if Menu/Tools/CellConten/Autocalculate is on, if it doesn't work, try a hard recalc [Ctrl+Shif+F9].

m.a.riosv gravatar imagem.a.riosv ( 2014-03-22 13:57:26 +0100 )edit

Just to thank maiosv - I had this same problem and the autocalculate fixed it - I couldn't find it before - many thanks!

helenb gravatar imagehelenb ( 2014-07-26 17:30:41 +0100 )edit

answered 2014-03-08 10:44:30 +0100

ROSt52 gravatar image

updated 2014-03-08 15:19:02 +0100

Looking at what you did, I can't find anything wrong. Additionally copying formulas down work in general, I use this function very often.

Let's have a look at your spreadsheet after you attached it. Make sure that no confidential data are included.

I would additionally do:
in 1. column 3 rows of simple figures like 10,20,30
in 2. column 3 rows of simple figures like 1,2,3
in 3. column the difference for the first row and copy down using the handle.
in a new Calc file
in the same Calc file but new sheet
in the same Calc file and on the trouble making sheet.

I also would use the "Trace Precedence" functionality to see what will be shown then.

Should you do this, it would be helpful to report the resutls.

edit flag offensive delete link more


Given 2 columns with data newly typed, addition of columns by dragging a formula cell in the same problem sheet has the same result described above. Addition of cols in this way in a new worksheet in the same workbook also has the same result as above. Addition of cols in this way in a newly created spreadsheet works fine. In the newly created sheet in the original workbook, tracing either precedents or antecedents updates the values! & new formulas work! But existing ones formulas not updated.

andrewH gravatar imageandrewH ( 2014-03-22 01:23:59 +0100 )edit

Question Tools

1 follower


Asked: 2014-03-08 06:54:45 +0100

Seen: 3,508 times

Last updated: Mar 22 '14