Results 1 to 7 of 7
  1. #1
    New Lounger
    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.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 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.

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

    wolfief (2015-03-10)

  4. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 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! HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    wolfief (2015-03-10)

  6. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 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
    Attached Files Attached Files

  7. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 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
    Note: this could have also been done using the Application.ConvertFormula command
    Attached Files Attached Files
    Last edited by Maudibe; 2015-03-11 at 05:23. Reason: added file

  8. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  9. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 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

Posting Permissions

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