Sum and average differently weighted scores (never mind)

invisigirl

Board Regular
Joined
Mar 18, 2002
Messages
130
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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,144,311
Messages
5,723,643
Members
422,508
Latest member
Lordkit1

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?

Disable AdBlock

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
Top