Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Mar 2004
    Posts
    461
    Thanks
    37
    Thanked 1 Time in 1 Post

    Excel - effect on formulas when sorting columns

    I am needing to sort by the data in a column in an Excel spreadsheet and I need know the effect it will have on formulas.

    I've done a few tests, and it seems like if the formulas only refer to other cells that are on the same line as the cell with the formula, and only use relative cell references, that the formulas will still return correct results after sorting by the data in a column.

    Am I correct in this assumption?

    Thanks
    Last edited by Vincenzo; 2011-02-21 at 14:16.

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Sorting formulas is essentially the same as copying formulas. If a formula goes from row 2 to row 4 and that would be the same as if the formula were copied from row 2 to row 4 then there is not problem with the formulas. if copying the formulas would result in different results the sort would not work as desired.

    Steve

  4. #3
    4 Star Lounger
    Join Date
    Mar 2004
    Posts
    461
    Thanks
    37
    Thanked 1 Time in 1 Post
    I'm not following what you are saying. Seems like you are talking about copying just a formula, as opposed to moving an entire row and its data. Seems like what applies in one case would not necessarily apply in the other.

    If a formula is copied from one cell to another, my understanding is that it would then be using new data in its calculation, that new data being the data in the newly referenced cells that are called for by the formula when it is in its new location.

    But what I am hoping to confirm is that the same data would be used by the formula, since the cells that are referenced by the formula are on the same row and as such are being moved with the formula when the sort is done.

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 784 Times in 718 Posts

    Sort Formula Example

    Vincenzo,

    Maybe this will help explain the results of full row sorts with formulas.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Vincenzo (2011-02-22)

  7. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    The effect on the formula after a sort is the same as copying. A sort does not move the row contents, it copies them. The formula may (or may not depending on the abs/relative references) use new data, just as a copied formula does.

    My point is that if the formula can be copied from 1 row to another and the effects don't change, the sorting will not affect the formula results. If the formulas are so complex and so dependent on position that copying them changes the relative contents, then don't sort them.

    The sorted formulas are not moved. A formula that starts in A3 and ends up in A5 would be the same as if copied from A3 to A5. So if it refered to a cell in B3 and C3 and they were included in the sort, it will use the same values but it would not be using B5 and C5. It will use the same absolute references and the relative row references will be changed.

    Note though that a formula not in the sort that used A3 before the sort will still use A3 after the sort, it will not point to A5 (as MOVING would do).

    Steve
    Last edited by sdckapr; 2011-02-21 at 18:29.

  8. The Following User Says Thank You to sdckapr For This Useful Post:

    Vincenzo (2011-02-22)

  9. #6
    4 Star Lounger
    Join Date
    Mar 2004
    Posts
    461
    Thanks
    37
    Thanked 1 Time in 1 Post
    sdckapr,

    Thanks for the info, it helped clear things up. But one point still confused me.

    You said:

    A formula that starts in A3 and ends up in A5 would be the same as if copied from A3 to A5. So if it refered to a cell in B3 and C3 and they were included in the sort, it will use the same values but it would not be using B5 and C5. It will use the same absolute references and the relative row references will be changed.

    This does not make sense to me. If the formula was essentially copied from A3 to A5, then it would then be using B5 and C5, as implied when you said the relative row references would be changed.

    Anyway, it seems like I can conclude that a sort as specified in my initial post (the formulas only refer to other cells that are on the same line as the cell with the formula, and only use relative cell references) would not have any negative effect on the results of the formulas.

    I think that is what RetiredGeek was alluding to in his post, but since he just posted it and ran without any insightful conclusions, leaving it as an exercise for the student, I cannot be sure
    Last edited by Vincenzo; 2011-02-22 at 10:52.

  10. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Sorry I wrote it wrong (poor proofreading). I meant it would NO longer be using the cells B3 and C3, but instead would be using B5 and C5. The copied formula would return the same numbers since the values that were in B3 and C3 would now be in B5 and C5.

    Again I apologize for the confusion. I think you understand the nature of the sort as copy as opposed to move.

    Steve

Posting Permissions

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