# Find range of top n results in column

• ### 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,

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

Perhaps this is now impossible.

Thanks,

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

### All replies

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,

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 Thursday, November 16, 2017 12:36 AM
Wednesday, November 1, 2017 9:55 PM