Results 1 to 7 of 7
Thread: convert the numerical result of a formula so that it can be sorted without the formula operating

20150310, 13:58 #1
 Join Date
 Mar 2015
 Posts
 1
 Thanks
 2
 Thanked 0 Times in 0 Posts
convert the numerical result of a formula so that it can be sorted without the formula operating
A column of numbers is created. in a new column, entries in the column are compared using a formula which yields a new number. When this new column is sorted, the sort fails because, as I see it, the formula is still operating.
the formula is =L2/L1, L3/L1,L4/L1 etc. so if theL3/L1 is greater than L2/L1, then it moves L3/L1 to row L2 and now L2/L1 resides in L3 etc. and the values are not in numerical order. What is the secret to instructing SORT the value, not the formula.

20150310, 15:43 #2
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,575
 Thanks
 44
 Thanked 73 Times in 69 Posts
With relative cell references, the cells shift as you discovered. I think your only option is to select all of those cells, copy them, and paste special the values in another column. Sort on the basis of that new column.

The Following User Says Thank You to kweaver For This Useful Post:
wolfief (20150310)

20150310, 15:57 #3
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,950
 Thanks
 422
 Thanked 1,605 Times in 1,449 Posts
Wolfief,
Welcome to the Lounge as a new poster!
Another option would be to code your formulas with absolute referenced, [noparse]=$L$2/$L$1[noparse]. Of course, this means that you can't fill the formula down you have enter it for each row! HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

The Following User Says Thank You to RetiredGeek For This Useful Post:
wolfief (20150310)

20150310, 17:20 #4
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,412
 Thanks
 164
 Thanked 641 Times in 609 Posts
Hi
The trick to doing this is to use a second sheet to refer to the column of ratios on the first sheet.
Then, on the sheet that has these ratios, have another column which refers to the numbers on the second sheet. And then sort these numbers.
see attached
zeddy

20150311, 04:19 #5
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,979
 Thanks
 156
 Thanked 771 Times in 703 Posts
Wolfief,
The sort can be done with VBA using only the column that contains the formulas (col M) while preserving the original formulas. This is based off of RG's premise of absolute referencing. Highlight the cells in column M you wish to sort then click the sort button.
HTH,
Maud
Prior to sort:
SortFormulas1.png
After the sort:
SortFormulas2.png
Code:Public Sub SortRatio() On Error GoTo errorhandler ' 'DECLARE AND SET VARIABLES Dim cell As Range Dim str As String ' 'CHANGE RANGE TO ABSOLUTE REFERENCING For Each cell In Selection str = cell.Formula str = Left(str, 1) & "$" & Mid(str, 2, 1) & "$" & Mid(str, 3, Len(str)  2) cell.Formula = str Next cell ' 'SORT FORMULAS ActiveSheet.Sort.SortFields.Clear ActiveSheet.Sort.SortFields.Add Key:=Selection _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveSheet.Sort .SetRange Selection .Apply End With ' 'CHANGE RANGE BACK TO RELATIVE REFERENCING For Each cell In Selection str = cell.Formula str = WorksheetFunction.Substitute(str, "$", "", 1) str = WorksheetFunction.Substitute(str, "$", "", 1) cell.Formula = str Next cell Exit Sub ' 'HANDLE ERRORS errorhandler: MsgBox "Please select cells to sort containing the formulas" End Sub
Last edited by Maudibe; 20150311 at 04:23. Reason: added file

20150311, 06:10 #6
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,349
 Thanks
 4
 Thanked 228 Times in 210 Posts
If all the numbers are divided by the same denominator, just sort the number column and the ratio will follow suit automatically.
Regards,
Rory
Microsoft MVP  Excel

20150311, 15:25 #7
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,979
 Thanks
 156
 Thanked 771 Times in 703 Posts
Here is the amended code using the Application.ConvertFormula method instead to covert from relative to absolute referencing and back.
Code:Public Sub SortRatio() On Error GoTo errorhandler ' 'DECLARE AND SET VARIABLES Dim cell As Range Dim str As String ' 'CHANGE RANGE TO ABSOLUTE REFERENCING For Each cell In Selection str = cell.Formula cell.Formula = Application.ConvertFormula(Formula:=str, _ FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute) Next cell ' 'SORT FORMULAS ActiveSheet.Sort.SortFields.Clear ActiveSheet.Sort.SortFields.Add Key:=Selection _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveSheet.Sort .SetRange Selection .Apply End With ' 'CHANGE RANGE BACK TO RELATIVE REFERENCING For Each cell In Selection str = cell.Formula cell.Formula = Application.ConvertFormula(Formula:=str, _ FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative) Next cell Exit Sub ' 'HANDLE ERRORS errorhandler: MsgBox "Please select cells to sort containing the formulas" End Sub