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

[queenovague]

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!!

[FDibbins]

Hi and welcome to the forum

1st off, taking an average of averages is mathematically incorrect.

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.

based on that example, you need to add the attendance for all 12 weeks/possible attendance for those same 12 weeks.

try =countif(range-with-names,"name")/count(date-range)

If you still have a problem, upload a sample workbook and I will see what we can come up with
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[daddylonglegs]

I could calculate her average attendance with (80x10)+(100x2)/total weeks.....

This is the correct approach - you can use a SUMPRODUCT function to get the numerator (the (80x10)+(100x2) part) and a SUMIF formula for the denominator (total weeks), so if you have, for example, names in A2:A100, % attendance in B2:B100 and weeks in C2:C100 then for a specific name in E2 use this formula in F2 to get the average attendance

=SUMPRODUCT((A$2:A$100=E2)+0,B$2:B$100,C$2:C$100)/SUMIF(A$2:A$100,E2,C$2:C$100)

This is a "weighted average" formula and will work whether you have a single entry for a specific student or multiple entries

[XOR LX]

...or all SUMPRODUCTS:

=SUMPRODUCT(($B$5:$B$198=A38)*$DO$5:$DO$198*$DP$5:$DP$198)/SUMPRODUCT(($B$5:$B$198=A38)*$DP$5:$DP$198)

Assumed your Attendance % was column DO and No. of Weeks column DP.

Regards

[swhitesell]

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

[daddylonglegs]

...so if you use my suggested approach you can get the same results without the helper column, i.e. with this formula in J2 copied down

=SUMPRODUCT((B$2:B$7=I2)+0,C$2:C$7,D$2:D$7)/SUMIF(B$2:B$7,I2,D$2:D$7)

[queenovague]

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.