AVERAGE help, using it on formula returns.

Hey all, basically I work in a school and have a massive list (some 1200 pupils) of grades (over 35 each) in an excel 2010 workbook.
For each grade, we have a lookup table returning a value for the grade, so we can work with the numbers in the sheet and generate reports.
ie, A = 52, B = 46, and so on.
Now what I need is the Average number for entire classes but I have hit a wall.

I have obviously used =AVERAGE(N5:N250) and half the time it works, but often it brings up a #N/A because a grade isn't present somewhere in the list of students. Because not every student gets a result grade in every subject.

Is there any average formula's i can use, that will bypass the N/A's and just count the number values being returned from my lookup table?

Currently its a case of copy/paste just the values into new columns or manually deleting the lookup formula from cells next to a no grade entry, but as I'm sure you can imagine, for all those students and all those classes its highly time consuming.

Any help would be massively appreciated :D

So do you have #N/A results in the N5:N250 range that is causing the Average formula to give you #N/A error? Text and blanks in that range will be ignored by AVERAGE()....

Hi

You need a simple IF test in your formula that is looking up the grade.
For example in the Grade letter is in column B, then use something like
=IF(B2="","",VLOOKUP(B2,Grades,2,0))

Hi

Let me give you another option...

Let's say that you data(with #NA, values, are in A1:A18)

Then to avoid the #NA result, use not AVERAGE function but SUMIF with COUNTIF..

So in an empty cell, try this.

=SUMIF(A1:A18;">0")/COUNTIF(A1:A18;">0")

Hope to helps you.

Take a look to the sample..

You are Welcome.

Really, glad to helped you


Sometimes, if you can not go staight, tou have to use other ways, to take your result.(Generally in life, not special in Excel)