# Thread: convert the numerical result of a formula so that it can be sorted without the formula operating

1. ## 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.

2. 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.

3. ## The Following User Says Thank You to kweaver For This Useful Post:

wolfief (2015-03-10)

4. 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! HTH

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

wolfief (2015-03-10)

6. 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

7. 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
, 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```
Note: this could have also been done using the Application.ConvertFormula command

8. If all the numbers are divided by the same denominator, just sort the number column and the ratio will follow suit automatically.

9. 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
, 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```

#### Posting Permissions

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