Help with If Function

[Crosby87]

HI All,

I need to create a spreadsheet to "assign" a certain value to an individual score i.e. if Joe bloggs scores 85% for attendance he receives x points, if he receives 44 for performance he receives x points.

The Layout of my table needs to be similar to the the one below

Name Sickness Punctuality Performance
Joe Bloggs 85% 90% 54%
Joe Bloggs 2 25% 15% 85%
Joe Bloggs 3 50 50 50

i am presuming i would then need to have a data range table to show what points it would be for the certain score. i.e

Range Sickness Punctuality Performance
0-10% 5 5 5

Has anyone any idea on how i would do this or what functiosn to use??

THanks in advance

 

[VoG]

It is not entirely clear what you want to do but maybe something like

=LOOKUP(A1,{0.5,0.75,1},{"Lazy","OK","Super"})

 

[Crosby87]

Sorry for not making myself clear.

Basically we are reviewing staffing levels at work and they are being marked on certain criteria. They are to be scored by given a certain percentage, with that percentage being worth x points. I.e a score of 50% would be equal to 50 points. The problem being that Each of the criteria is likely to have a different weighting value in terms of points v percentage.

 

[VoG]

Please use this http://www.excel-jeanie-html.de/html/hlp_schnell_en.php to show what you expect before and after.

 

[Crosby87]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	CSA	Score 1	Points	Score 2	Points	Score 3	Points		Range		Score 1 Points	Score 2 Points	Score 3 Points
2	Joe Bloggs	50		75		85			0	10	15	5	25
3									11	20	20	10	50
4									21	30	25	15	75
5									31	40	30	20	100
6									41	50	50	30	125
7									51	60	60	40	150
8									61	70	70	50	175
9									71	80	80	75	200
10									81	90	90	85	225
11									91	100	100	100	250
Sheet1

The table to the right shows example points scores for each percentage range for each category, labelled Score 1, 2 and 3 (which would be percentage).

For Example, in Cells C2, E2 and G2, the values from from K6 (score1) l9, (Score 2) and M10 for Score 3.

There are going to be roughly 225, jo bloggs so i need a formula of some sort to help me out.

Thanks in advance

 

[Crosby87]

Anybody have any ideas?

 

[snowblizz]

Does this work, it is split in two as the HTML maker only supports 12 columns in one go.

Excel Workbook
	A	B	C	D	E	F	G
1	CSA	Score 1	Points	Score 2	Points	Score 3	Points
2	Joe Bloggs	51	60	75	75	85	225
3
4
5
6
7
8
9
10
11
Sheet3

Excel 2007

Cell Formulas
Range	Formula
C2	=INDEX($K$2:$K$11,MATCH(B2,$I$2:$I$11,1))
E2	=INDEX($L$2:$L$11,MATCH(D2,$I$2:$I$11,1))
G2	=INDEX($M$2:$M$11,MATCH(F2,$I$2:$I$11,1))

Excel Workbook
	H	I	J	K	L	M
1		Range		Score 1 Points	Score 2 Points	Score 3 Points
2		0		15	5	25
3		11		20	10	50
4		21		25	15	75
5		31		30	20	100
6		41		50	30	125
7		51		60	40	150
8		61		70	50	175
9		71		80	75	200
10		81		90	85	225
11		91		100	100	250
Sheet3

Excel 2007

 

[Crosby87]

Brilliant Thanks Very Much