Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Statistics Problem
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Statistics Problem
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. "Alex Lifeson" wrote in message 2... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Statistics Problem
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Alex Lifeson" wrote in message 2... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Statistics Problem
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 -- Regards, Tom Ogilvy "Alex Lifeson" wrote in message 2... 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Statistics Problem
there are built in worksheet functions
rank percentile quartile percentrank max min large small see Excel help for details -- Regards, Tom Ogilvy "Alex Lifeson" wrote in message 2... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Statistics | New Users to Excel | |||
Statistics problem | Excel Worksheet Functions | |||
Statistics | Excel Discussion (Misc queries) | |||
Statistics | Excel Discussion (Misc queries) | |||
statistics problem | New Users to Excel |