Results 1 to 4 of 4
2011-02-23, 10:16 #1
Excel-applying formulas to sorted data
I sorted the data on an Excel spreadsheet and passed the sheet along to someone who was then applying a formula to a column of data by inputting it in the top cell and dragging it down the column. When we reviewed it, we found some inconsistencies in the formula as it appeared in cells down the column - ie the relative cell references were incorrect in about 10 contiguous rows, they showed an incorrect row number.
I am trying to determine if this was human error, or if there is some issue with using sorted data that I need to know about.
Once a sheet has been sorted, are there any limitations or precautions that I need to be aware of when applying formulas (or anything else) to that sorted data? I ask this because I found out yesterday that filtered data requires some special consideration.
Last edited by Vincenzo; 2011-02-23 at 10:22.
2011-02-23, 10:36 #2
- Join Date
- Dec 2009
- East Coast, USA
- Thanked 43 Times in 43 Posts
2011-02-23, 11:55 #3
- Join Date
- Jul 2002
- Pittsburgh, Pennsylvania, USA
- Thanked 342 Times in 335 Posts
My initial presumption is that something was moved, misaligning some of the data and then this offset was repeated (as would be expected) with the copying. I owuld start looking to see if a value or values got moved and the formula refering to those values was not moved along with it...
2011-02-23, 13:30 #4
I've been unable to reproduce the error.
The person who worked on it says they did not knowingly move any data. All they did was create a formula in an empty column and drag it down to put it in a block of cells below. Then a few rows down, created a slightly different formula, and dragged it down too.
The formula was a simple division - the dividend is a relative cell reference of the value in a cell two columns to the left, on the same row. The cells with the error in the formula are showing relative cell references to the correct column, but the cells referenced are about 100 or so rows down the page. There are about 10 of these errors in a group in the column.
Last edited by Vincenzo; 2011-02-23 at 13:37.