ranking/sorting data automatically as new data is added

[Roller]

I am trying to sort/rank a table by the average of several rounds/trials. Each subject is in a row and the number result from each trial is in the columns. I'd like a way to rank the subjects (in a separate table) after every 5 trials. I've played around with the autofilter but cannot seem to make it work. Thanks in advance.

Bob R.

 

[Peter_SSs]

Roller

Welcome to the Mr Excel borad!

Any chance of giving us a small sample of data together with expected results? It might make your explanation easier to understand. The best (though not only) way to show a sample of your sheet on this board is to use Colo's HTML Maker. Here's how:
http://www.mrexcel.com/board2/viewtopic.php?t=92622

 

[Roller]

Without playing around with the HTML Maker, here's what I was thinking:
Subjects a, b, and c are being tested.

name round 1 round 2 round 3 average
subject a 10 10
subject b 5 5
subject c 0 0
I'd like to have the subjects sorted by average per round (a,b,c). Once the data is entered for round 2, the sheet should re-sort automatically based on the results.

name round 1 round 2 round 3 average
subject a 10 1 5.5
subject b 5 7 6
subject c 0 13 6.5
The new result should display subject c, then b, then a.

I want to use the same data table without having to re-sort each time. I'd like to be able to apply a macro that returns the ranking based on the round being variable.

 

[Peter_SSs]

See if this is headed in the right direction.

1. B6 (copied across): =COUNT(B2:B4)
2. G2 (copied down): =AVERAGE(B2:F2)
3. H2 (copied down): =RANK(G2,$G$2:$G$4)
4. This code for the Worksheet_Change event<font face=Courier New><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target<SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> DataArea<SPAN style="color:#00007F">As</SPAN> Range
    
    <SPAN style="color:#00007F">Set</SPAN> DataArea = Range("B2:F4")
    
    <SPAN style="color:#00007F">If</SPAN> Target.Count<> 1<SPAN style="color:#00007F">Then</SPAN><SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Intersect(Target, DataArea)<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN><SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Cells(6, Target.Column)<> 3<SPAN style="color:#00007F">Then</SPAN><SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN>
    
    Application.EnableEvents =<SPAN style="color:#00007F">False</SPAN>
    Application.ScreenUpdating =<SPAN style="color:#00007F">False</SPAN>
    
    Range("A1:H4").Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    
    Application.EnableEvents =<SPAN style="color:#00007F">True</SPAN>
    Application.ScreenUpdating =<SPAN style="color:#00007F">True</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>

Mr Excel.xls
	A	B	C	D	E	F	G	H	I
1	Name	Round 1	Round 2	Round 3	Round 4	Round 5	Average	Rank
2	Subject a	10	23				16.5	1
3	Subject c	8	14				11	2
4	Subject b	5	6				5.5	3
5
6	Round Entries	3	3	0	0	0
7
Sorting