MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


Posted by Daniel Cremin on January 29, 2002 12:53 PM

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.


Posted by Mark W. on January 29, 2002 12:57 PM

Use the array formula...

{=AVERAGE(IF(ISNUMBER(range),range))}

where range is the cell range contains your scores.

Note: Array formulas must be entered using the
Control+Shift+Enter key combination. The
outermost braces, {}, are not entered by you --
they're supplied by Excel in recognition of a
properly entered array formula.

Posted by Daniel on January 29, 2002 1:04 PM

Thanks for the quick answer. I just realised i completly muddled up my question though. Im actually looking at getting separate class lists off one main page of data, again through the use of If Statements that create an index no, if a specific cell has a teachers name in it. Its basically the same principal as before. What i need is a simple way to get only the students in Mr Dickens class shown on this report to have their data summariese (the other student cell s are hidden). is there a way to average only visible row cells, or some way i can get the system to do an average of the mark cells of all rows with a number greater than 0 in the index no column. Thanks for the quick response b4

Posted by Mark W. on January 29, 2002 1:09 PM

Just change the IF condition...

{=AVERAGE(IF(index>0,grades))}

Posted by Daniel on January 29, 2002 1:22 PM

Re: Just change the IF condition...

Thanks!!! I can just have the average being cells with an actual index no being avgd.
=AVERAGE(IF B3:B33>0, C3:C33) by this i mean look at the index nos in B3 to B33 and if its greater than 0 then average this row's cell within in the range c3:c33-but not ones with 0s. Will this work do you think. I didnt understand why you need 3 sets of brackets.


Posted by Aladin Akyurek on January 29, 2002 2:25 PM

Re: Just change the IF condition...

=AVERAGE(IF B3:B33>0, C3:C33)

will not work. But

=AVERAGE(IF(B3:B33,C3:C33)), array-entered, will.

Aladin