# Sum and average differently weighted scores (never mind)

#### invisigirl

##### Board Regular
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

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Replies
10
Views
1K
Replies
4
Views
279
Replies
2
Views
476
Replies
4
Views
329
Replies
4
Views
244

1,218,740
Messages
6,144,215
Members
450,531
Latest member
avril18

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back