Sum and average differently weighted scores (never mind)

[invisigirl]

Part one: I have a spreadsheet with 6 columns of scores, presented as letters. So I will probably need a MATCH or VLOOKUP to bring back the corresponding numerical scores.

Part two: The first 5 columns' scores are to be given equal weight, equaling 50% of the total score. The 6th column's score is to be given the remaining 50% of the weight.

Part three: I have 20 spreadsheets like this (they're employee evaluations), but not every employee has 6 columns of scores. Some may have 5 or 7 instead. The last column will always be the "weightier" one.

Overall question: Is there a formula I can use to a) count the number of scores (this isn't the biggest deal), b) look up the values for each of the letters (I: -10, T: 5, S: 5, O: 10, C: 15), and c) sum the first five scores with the 6th score, something like this: (F1+G1+H1+I1+J1)+K1, giving them the different weights I noted earlier?

Here is sample data:

F1 | G1 | H1 | I1 | J1 | K1 | TOTAL
S | S | S | O | O | O | ?


I'm sure that won't line up, but the straight vertical lines represent column breaks. The top line is the cell location, and the second line is the data contained therein.

Does this make any sense? I know that I could use a VLOOKUP and sum the first 5 columns in one cell, and then use another cell to do the rest of the calculations, but I wondered if there was a way to do it all in one cell.

Any help would be met with squeals of glee (I can do that because I have to have my door closed when working on employee evaluations).

April
This message was edited by invisigirl on 2002-10-03 14:34
This message was edited by invisigirl on 2002-10-03 14:35
This message was edited by invisigirl on 2002-10-03 14:52