Stuck on which formula to use...SUMIF, AVERAGE,

Hi Forum users,

I hope you can help me, I have been baffled by this for weeks..

Basically I am trying to calculate a student's average attendance over a period of time.

I was using the VLOOKUP formula but realised that this would only find the first occurrence of the students name in a column, whereas some students names appear more than once if they have been promoted to another class.

The old formula I was using worked fine if the name appeared only once:
=IF($A38="","",VLOOKUP($A38,Morning!$B$5:$DQ$198,118,FALSE))

(Where $A38 is the student's name on my summary worksheet)

I need a formula that can work out the average percentage of either one or more percentages, divided by a certain number of weeks.

For example, Abbey had 80% attendance for 10 weeks then was promoted to Level 2 and since then her attendance has been 100% for 2 weeks.


Class | Attendance | no. weeks

Level 1
Abbey | 80% | 10
George | 90% | 12
Steven | 87% | 12

Level 2
Abbey | 100% | 2
Gertrude | 95% | 12
Fern | 100% | 12


I could calculate her average attendance with (80x10)+(100x2)/total weeks, but I need a formula that I can use for all the students, whether their names appear just once or more than once.

So basically I need it to average the percentages IF the students name appears more than once in the column, (and the formula would have to find the row the students name appears in then the column of data required).

Hope this makes sense...? N


I'm using Excel 2003 so AVERAGEIF forumula is out. I've have tried many variations of formulas including SUMIF, AVERAGE, COUNTIF, etc but none seem to work.

Please help!!

An example of what FDibbins is saying is Abby attended 80% of her 10 weeks in level 1 and 100% of her 2 weeks in level 2. This results in an attendance of 10 weeks out of a possible 12, or 83.33%

Here is a sample spreadsheet solving the problem. Notice I had to add another column to your data.

attendance_example.xlsx

Wow you guys are amazing, not to mention fast!

Thanks for all the great replies.

I already have a helper column set up on my summary worksheet so was no trouble.
SumProduct works a charm, I would have never thought of using it with SumIf so thank you!

Next time I will just ask on here first rather than spending hours trawling the net looking for answers.