Results 1 to 4 of 4
  1. #1
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    654
    Thanks
    95
    Thanked 14 Times in 13 Posts

    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.

    Thanks
    Last edited by Vincenzo; 2011-02-23 at 10:22.

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Quote Originally Posted by Vincenzo View Post
    .......... 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.
    ..........
    If the dragging process is repeated, do the errors appear again in the same places?

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    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...

    Steve

  4. #4
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    654
    Thanks
    95
    Thanked 14 Times in 13 Posts
    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.

    Thanks
    Last edited by Vincenzo; 2011-02-23 at 13:37.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •