Tough One!!!- How can i get an AverageIf Function

Ok heres my problem. Ive got a system of Student Attainment where if a student over-performs on a test (gets a score level) at least 1.0 higher than tehy were predicted 4 or more times then they appear on a special list report of consistently overperforming students. The easist thing to do would be to use a filtering macro to get them across but i dont like that way; instead i have chosen to solve this problem by having a bunch of special If statements that provide a number 1,2,3 etc if the first student in the list of 30 (C8:C38) has over-performed 3 or more times. The if statement looks at the specific Over-performance counting cell and if the value is 3 or greater it writes 1 in the cell, but 0 if less. For the second If Statement its the same except it looks at the second student Over-performance cell and writes 2 if its a match an 0 if it isnt greater than 3. This goes on for the 30 students. Next to this i have a load of index formulas through which the student names appear, as well as their test marks. The Index formulas look down the list of student names, and find the name that matches the given index (1 if its the first student line, 2 if its the seconds,etc and if its 0 then it leaves it blank). The smart bit is that i have a macro that hides the rows with 0s in the Index column so that only over-performing students are shown. The thing is i want to be able get an average of these students test marks, but how can i do this as some student rows will have marks in them but others will have Error "Value" written in them. can anyone else think of a sophisticated method (its for a computer project) of accomlishing this as ive racked my brains for hours but have hit the edge of my excel expertise. Please Please help me sort this one out. Thanks a lot.