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
Last edited by PhoenixJR; 01-24-2012 at 04:52 AM.
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()....
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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))
--
Regards
Roger Govier
Microsoft Excel MVP
Sorry if I didn't make myself entirely clear.
@NBVC I currently have the standard AVERAGE(), and yes there are #N/A errors in that column. But whenever one appears it always returns an #N/A error on the average. So I presumed I needed something to tell it to ignore the #N/A's or something...
Is it not ignoring them because its a formula return/answer? =VLOOKUP(O20,GHOST!$I$2:$J$17,2,FALSE) to be precise.
@Roger the column i need it pulling from is the numerical value we have given to each grade. So literally everything in the column i need the average of is a number... UNLESS the person didnt score a grade in which case the formula is returning with an N/A which is where my issue lays.
I don't suppose there is any way of adding a "blank" value to my lookup table and it return a blank? So the average formula isn't affected that way? Could that be another way round the issue>
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.
Regards
Fotis.
I am proud that i am Greek.
Just to know every one.We Greeks, we are nοt proud of our politicians. Υou?
Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
My Avadar picture, is from Athens Acropolis.
http://www.theacropolismuseum.gr
http://www.visitgreece.gr/
Hmmmm It could be me being a noob but I cannot get that one working at all
Take a look to the sample..
Regards
Fotis.
I am proud that i am Greek.
Just to know every one.We Greeks, we are nοt proud of our politicians. Υou?
Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
My Avadar picture, is from Athens Acropolis.
http://www.theacropolismuseum.gr
http://www.visitgreece.gr/
Thanks so much man, thats definately working on the sample sheet, I will try to get it working on my pupil master sheet.Originally Posted by Fotis1991
Your help is very much appreciatedMore than happy to give you rep ^_^
Cant thank you enough!
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)
Regards
Fotis.
I am proud that i am Greek.
Just to know every one.We Greeks, we are nοt proud of our politicians. Υou?
Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
My Avadar picture, is from Athens Acropolis.
http://www.theacropolismuseum.gr
http://www.visitgreece.gr/
Never a truer word spoken, just got it working on my workbook also, thanks again, much appreciated ^_^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)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks