AverageIFS grading formula

dshafique

Board Regular
Joined
Jun 19, 2017
Messages
144
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.

Code:
=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
 

Bratamoli

New Member
Joined
Jul 20, 2018
Messages
19
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)
Code:
=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:
Code:
=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:
Code:
=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.
 

Forum statistics

Threads
1,081,556
Messages
5,359,555
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top