Using the AVERAGE() function w/ Referenced Letters

[Ahyastigi]

I'm creating a spreadsheet that will allow me to track evaluations completed on multiple individuals, ultimately allowing me to see where all individuals are ranked against each other. In these evaluations an individual is graded in several categories with a letter from A to G. These letters can be associated with the numbers 1-7 respectively. Additionally, the letter H is used in an instance where a grade in that category is not applicable. The end state for this spreadsheet is to give me the average letter grade and average point value for an individual as well as his or her ranking against the others in the spreadsheet.

I have been able to associate A-G with 1-7 with VLOOKUP() and have been able to get and average point value, average letter grade, and ranking to work. The problem is when I plug H into a category. Depending on how I write the formula it will either associate H with 0 and throw my point values off or give me a value error. I want the function to ignore H when it is plugged in and figure the averages without it.

This is the current function I am playing with that returns and value error:

=AVERAGE(VLOOKUP(E3,Sheet2!$A$1:$B$8,2),VLOOKUP(F3,Sheet2!$A$1:$B$8,2),VLOOKUP(G3,Sheet2!$A$1:$B$8,2),VLOOKUP(H3,Sheet2!$A$1:$B$8,2),VLOOKUP(I3,Sheet2!$A$1:$B$8,2),VLOOKUP(J3,Sheet2!$A$1:$B$8,2),VLOOKUP(K3,Sheet2!$A$1:$B$8,2),VLOOKUP(L3,Sheet2!$A$1:$B$8,2),VLOOKUP(M3,Sheet2!$A$1:$B$8,2),VLOOKUP(N3,Sheet2!$A$1:$B$8,2),VLOOKUP(O3,Sheet2!$A$1:$B$8,2),VLOOKUP(P3,Sheet2!$A$1:$B$8,2),VLOOKUP(Q3,Sheet2!$A$1:$B$8,2),IF(R3="H"," ",VLOOKUP(R3,Sheet2!$A$1:$B$8,2)))

The last IF() statement for cell R3 is the trial I'm running to try to get the function to ignore H. When I figure it out I can populate the other cell "lookups" with this function.

I have seen versions of this type of evaluation tracker before, however, they have hidden cells in row 1000 or so that will calculate the point value for each category. This works, but only allows a finite number of individuals to be added without going back and creating more of the hidden cells.

Kind of a complicated explanation, but I want this spreadsheet to work without hidden cells beyond the table used on sheet 2 to associate the letter and number values. Any help is greatly appreciated.

 

[barry houdini]

Hello Ahyastigi, welcome to MrExcel

If A = 1 through to G =7 you could do that without a lookup table, e.g.

=AVERAGE(IF(E3:R3<>"H",IF(E3:R3<>"",CODE(E3:R3)-64)))

That's an array formula that needs to be confirmed with CTRL+SHIFT+ENTER

That ignores H and blanks

 

[Ahyastigi]

Thanks, the fix worked great and I think my spreadsheet finally came together.