AverageIFS grading formula

[dshafique]

Hi everyone, I have a spreadsheet that keeps track of my students' weekly progress with an overall month summary at the end. every day they're graded on different things on a different scale. for example they're given 4 points every day for homework for a total of 20 per week or 80 a month. before I just had a formula which divides student's points over total possible points, but it wouldnt show an accurate report until and unless all the months are filled out. so I tried to do an average formula so it can show me the students current monthly average based on his current status in case we want to check their progress in the middle of the month or something. I tried out this formula but I keep gettign something wrong. any help would be greatly appreciated.

=IFERROR((AVERAGEIFS(('Week 1'!G5,'Week 2'!G5,'Week 3'!G5,'Week 4'!G5),'Week 1'!G5,">0",'Week 2'!G5,">0",'Week 3'!G5,">0",'Week 4'!G5,">0")/10),"")

i tried doing greater than 0 so it doesnt count scores which havent been recorded yet

thanks

 

[RasGhul]

Try

=AVERAGE(WEEK1:WEEK4!G5)

 

[Bratamoli]

It took me a minute or two, but I think I figured out what you're asking for.
I think you want a formula that doesn't just show the average number of points they have so far or total number of points they have so far, but rather divides that total by the number of points they COULD HAVE by now to give their percentage grade ... Is that about right or did I read too much into what you were saying?

The following formula would average each G5 with a number, then divide by (20 points possible per week in the above example)

=AVERAGE('Week 1'!G5,'Week 2'!G5,'Week 3'!G5,'Week 4'!G5)/20

Make sure the cell is formatted as a percentage, and it should be good.

One important note though:
If each weekly tab's G5 cell contains a formula (perhaps adding the "daily" points), make sure it goes something like this:

=IF(COUNT(A5:E5)=0,"",SUM(A5:E5))

That will cause the cell to be "blank" if there is not a grade yet for that week.

Next note: So far, this solution will show you their correct current grade at of the end of each week. During the week won't work right. On Tuesday (for example), it'll calculate as though they've earned 8 points out of 20 possible so far rather than 8 points out of 8 possible so far. If the daily scores are recorded in cells A5-E5, this might be a better solution:

=AVERAGE('Week 1'!A5:E5,'Week 2'!A5:E5,'Week 3'!A5:E5,'Week 4'!A5:E5)/4

If this still isn't quite what you're looking for, then definitely give us a little more info and we will be happy to help.