Average over a text range using a vlookup In Column 2nd

Hi,

i have posted it before and thought the problem was solved until i used the formula in a large spreadsheet and the return value didn't seem right!

i am trying convert Text in the numeric value and get the average

=SUMPRODUCT(SUMIF('grade Sheet'!$A$1:$A$24,$A$1:$A$8,'grade Sheet'!$B$1:$B$24))/COUNTA($A$1:$A$8)

i was using the above and it worked perfectly in a smaller spreadsheet, however it seems it is only return the value in certain array. when i used it in a more complex spreadsheet it didn't work

also i would like it to ignore all the "NA" as it divide to get the average value

i have attached the spreadsheet -

please help

Hi DonkeyOte,

thanks for you reply!

yes - it was a typo, it should be 'grade Sheet'!$A$2:$A$153

you replied a very similar question posted by me 2 days ago. so glad to see you and offer help again!!

what i wanted to do was - i wanted to covert the "Result" into numeric and work out the average. In order to do that i have created another sheet for vlookup, but becos there are more that 20 sheets in that excel file and it would take ages to covert all and get the average. so i posted the question and you replied and suggested this formula. i tried with the attachment (which only has the a few grade and a separate sheet called "grade sheet") worked perfectly fine. however as i use it on the actual one it seemed the return value didnt make sense.

please see the attachment - hope it makes more sense

Your determining the divisor based on Col F (Result) whereas you're basing the SUM of Grade points on Col B (Attainment Grade).
Either you should be using F or if you do want to use B you should discount the values in B according to NA status of F

i think i can manually delete all record marked "NA"

SUMIF is not a case sensitive search - so A/a will be treated as identical (and aggregated)

i dunno, am i correct, but i thought it work in vlookup it should work in SUMIF or not??

You have identical values repeated in your table - eg A:G & U are listed twice over in upper case form on Grade Sheet

you mean "grade sheet" row 145-153. yes, i will delete them ( cos i have been editing using and adding more grade )

any suggestion would be grateful to solve this mysteries