Can't figure out averaging to ignore blank cells

Hey people,

I am massively confused. I have been searching this forum and others for answers, and this has left me even more confused. I need the warm reassurance that only a personalised reply can provide! Excel is pretty much a complete mystery to me and the spreadsheet I have put together has been made entirely from following guidance on the net.

I am putting together a grade book. What I need is for the average marks of say two tests to be calculated into a percentage - using the marks achieved by the students divided by the maximum marks available. If the cell is blank (i.e. the student was absent for the assessment) I want that to be ignored in the calculation. Getting 15 out of 20 in one test and being absent for the other would therefore produce an average of 75%, for example.

The formula I have tried using is: =SUM(O3:P3) / (SUM(O$1:P$1) - COUNTIF(O3:P3,"<0")) where O3 and P3 are the marks achieved by the student and O1 and P1 are the maximum marks possible. It isn't working though and is still counting blank cells as zero and skewing the average. What exactly am I doing wrong?

All help very gratefully received!

Oh, I will attach the spreadsheet in its current form (I am using Excel for Mac 2011). The formula above relates to column I ("Reading Score), taking marks from columns O and P ("RA1" and "RA2").

I think what you're after is:

=SUM(O3:P3) / SUMIF(O3:P3,">0",O$1:P$1)

I don't think AVERAGEIF or AVERAGEIFS can be made to work in this scenario, because the function is determining a percentage, not an average. For the same reason any solution involving COUNTIF is going to return the wrong result.

Thank you for the speedy replies everyone - greatly appreciated. I was going to go for Andrew's suggestion (which worked
perfectly) but on balance perhaps daddylonglegs' is more appropriate. I don't anticipate anyone scoring a zero but it seems
sensible to cover my bases!

One other question, now that I have you - I have two tables in the spreadsheet: the one on top is for inputing and displays marks, percentages and grade; the one below is a grade summary designed to be more readable and for filling out certificates.

I can sort the values in the top table fine, but it doesn't seem to work on the bottom table - I am using the =""& function to mirror the values from the top table, and thought perhaps that might be the problem. Any thoughts?

PS. I realise I can just sort the top table is I want, but would like the convenience of sorting directly in the bottom table if possible.

I have attached the updates spreadsheet. proving ground gradebook prev. vers..xls

OK great - would you mind walking me through it?

Also, any advice on my most recent post would be appreciated. Thanks.

Apologies - any chance of being a bit more detailed in the instructions? I'm really pretty lost when in comes to excel. Also, I'm using a macbook, so some of the shortcuts won't work for me.

Thanks for the ongoing help guys - my gradebook is back with a vengeance and (courtesy of a template I used) a lot prettier - see what you think.

My clumsy formulas aren't completely cutting the mustard though. You'll notice that if you sort by Course Grade (column E)the Total % column (column F) doesn't sort correspondingly.

Similarly, sorting by Performance doesn't sort it accurately in columns E and F. What is strange is that you can see the incell graphs in Performance ascend or descend in order - but that doesn't match the figures in the Total % column. Meaning that the incell graphs are showing more of these [|] for some of the lower percentages. Just can't figure out what's happening in those columns!hopefully compatablie.xls

I have been throwing this back and forth between Excel 2011 for Mac at home, and Windows Excel 2003 at work - the source of the problem?

Anyone available to help? It's quite a handsome spreadsheet I think, but I do need help behind the scenes!