none
Find range of top n results in column RRS feed

  • General discussion

  • Thank you for taking the time to read my question.

    We are sending grouped and sorted data to Excel for our users to work with in 2010 and 2016. We are using named ranges in the formulas within the spreadsheet.

    Each row in the spreadsheet within a group is a record which is sorted within the group. The record may or may not have values in each column. The user can specify that they would like the average of the most recent 20 records for each column, however the group could contain 50 records and the most recent 20 values could require the first 35 rows.

    We are not able to create our own user defined function as we would have to ensure all users have the function (which we can't). We are also unable to put our data into a template in which we could create our own user defined function.

    It would be nice to have something like:

    NamedRange = D5:D55
    Top = Returns a range of cells

    =Average(Top(NamedRange,20)) IF this was real, the "Top" function would look for the first 20 results moving from the first cell in Named Range (D5) towards the last cell in a named range (D55) until it was able to count 20 values (essentially a do until loop).

    So, the formula would end up looking like: Average(D5:40) because the first 20 values were found in the first 35 rows.


    Thank you for your help

    For Example:

    SampleID SampleDate Protein
    S6 28/01/2017
    S3 26/01/2017 8
    S4 11/01/2017
    S1 07/01/2017 2
    S5 02/01/2017 1
    S2 01/01/2017 6
         
    Average =AVERAGE(Top(rngProtein,3))
    Average =AVERAGE(E8:E11)
    Average 3.66666666666667

    Thank you for your time,

    Brad

    I just learned that we also cannot use array functions {}.

    Perhaps this is now impossible.

    Thanks,
    Brad

    • Edited by mbrad Monday, October 30, 2017 9:08 PM one more restriction:
    Monday, October 30, 2017 6:54 PM

All replies

  • Hi mbrad,

    I hope you will share a sample file via cloud storage such as OneDrive, Dropbox, etc.
    It is time-consuming task for me to make test data.
    (Remember to modify/edit your personal/important data before sharing.)

    Regards,

    Ashidacchi

    Wednesday, November 1, 2017 1:43 AM
  • Hi Ashidacchi,

    I don't have OneDrive or DropBox... If I were able to provide a file, it would look exactly like the table I posted.

    I was playing with Macros to see if I could make one work and made this:

    Function FindTop(tRng As Range, tCount As Long) As Variant
    Dim tValCount As Long
    Dim tRowCount As Long
    Dim cell As Range
    
    If tRng.Columns.Count > 1 Then 'More than one column selected
        FindTop = CVErr(2023)
    End If
    
    For Each cell In tRng
        If IsNumeric(cell.Value) Then
            tValCount = tValCount + 1
        End If
        tRowCount = tRowCount + 1
        If tValCount = tCount Then
            Exit For
        End If
    Next cell
    
    If tValCount < tCount Then
        FindTop = CVErr(2023) 'tCount not reached. Number of values not found in range given
    Else
        Set FindTop = Application.Range(Cells(tRng.Row, tRng.Column), Cells(tRowCount + tRng.Row - 1, tRng.Column))
    End If
    
    End Function
    

    to use this you would do something like =Average(FindTop(rngProtein,3)) It actually works great.

    The only problem is that I cannot use Macros or Array formulas. 

    Thanks,
    Brad

    Wednesday, November 1, 2017 9:33 PM
  • Hi Brad, (Good morning, in Japan)

    I provide my email address. Please send an email with attached file (your sample Excel file).
    *** (dot) *** (at) *** (dot) ***

    I will mask/delete this address in a few hours for my privacy.

    Regards,

    It's time-consuming task for me to make test data.
    (Remember to modify/edit your personal/important data before sharing.)


    Ashidacchi


    • Edited by Ashidacchi Thursday, November 16, 2017 12:36 AM
    Wednesday, November 1, 2017 9:55 PM