Results 1 to 7 of 7

20110221, 14:11 #1
 Join Date
 Mar 2004
 Posts
 460
 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?
ThanksLast edited by Vincenzo; 20110221 at 14:16.

Subscribe to our Windows Secrets Newsletter  It's Free!
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!
+ 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!

20110221, 16:47 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,131
 Thanks
 13
 Thanked 314 Times in 308 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

20110221, 17:15 #3
 Join Date
 Mar 2004
 Posts
 460
 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.

20110221, 17:34 #4
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 5,801
 Thanks
 185
 Thanked 699 Times in 637 Posts
Sort Formula Example
Vincenzo,
Maybe this will help explain the results of full row sorts with formulas.

The Following User Says Thank You to RetiredGeek For This Useful Post:
Vincenzo (20110222)

20110221, 18:26 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,131
 Thanks
 13
 Thanked 314 Times in 308 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).
SteveLast edited by sdckapr; 20110221 at 18:29.

The Following User Says Thank You to sdckapr For This Useful Post:
Vincenzo (20110222)

20110222, 10:47 #6
 Join Date
 Mar 2004
 Posts
 460
 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 sureLast edited by Vincenzo; 20110222 at 10:52.

20110222, 11:07 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,131
 Thanks
 13
 Thanked 314 Times in 308 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