Statistics Problem

[Alex Lifeson]

In vba code, I need to go through several rows (with 75 cols each) and pick
out the top 6 values PER ROW.

Are there any vba methods that would help me do this?

First, I thought I would sort and then pull of the first 6 cells. Can you
sort by Row? If not, should I transform the rows into cols and then sort
the cols?

Next, I have tried to find a Percentile / Rank or a Top N type function.

Any suggestions?

Thanks in advance!

 

[Mark Ivey]

I am not totally sure of what you need, but you might take a look into what
a pivot table could do for your data.

 

[Jon Peltier]

If you haven't given up on worksheet functions, you could use
=LARGE(range,1) through =LARGE(range,6), where range is the address of the
row of data. Maybe insert a sheet, and in cell A1 of the second sheet, enter
a formula like =LARGE(Sheet1!$A1:$BW1,COLUMN()). Fill it across to F1 and
down as many rows as needed.

- Jon

 

[Tom Ogilvy]

Sub AAVV()
For Each cell In Range(Cells(2, 1), Cells(2, 1).End(xlDown))
cell.EntireRow.Sort _
Key1:=cell, _
Order1:=xlDescending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlLeftToRight
Next
End Sub

 

[Tom Ogilvy]

there are built in worksheet functions

rank
percentile
quartile
percentrank
max
min
large
small

see Excel help for details