Help with If Function

Crosby87

New Member
Joined
Oct 6, 2009
Messages
42
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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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

New Member
Joined
Oct 6, 2009
Messages
42
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.
 

Crosby87

New Member
Joined
Oct 6, 2009
Messages
42

ADVERTISEMENT

Excel Workbook
ABCDEFGHIJKLM
1CSAScore 1PointsScore 2PointsScore 3PointsRangeScore 1 PointsScore 2 PointsScore 3 Points
2Joe Bloggs50758501015525
31120201050
42130251575
531403020100
641505030125
751606040150
861707050175
971808075200
1081909085225
1191100100100250
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
 

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
Does this work, it is split in two as the HTML maker only supports 12 columns in one go.
Excel Workbook
ABCDEFG
1CSAScore 1PointsScore 2PointsScore 3Points
2Joe Bloggs5160757585225
3
4
5
6
7
8
9
10
11
Sheet3
Excel 2007
Cell Formulas
RangeFormula
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
HIJKLM
1RangeScore 1 PointsScore 2 PointsScore 3 Points
2015525
311201050
421251575
5313020100
6415030125
7516040150
8617050175
9718075200
10819085225
1191100100250
Sheet3
Excel 2007
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,645
Members
414,083
Latest member
Mrsash

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