CODEWITHSUNDEEP
excel
Dani
Dani

Reputation: 21

Need help converting a range of numbers to text in excel

I'm using MS Excel 2010; I'm a total newbie to using functions, so be kind when answering.

I'm creating a sports grading sheet that will help instructors calculate the test scores for their students. So far I've set up excel to add a students individual points in a column and then give a percentage value out of 100%

What I want to do now is have the percentage value be converted to a selection of text result. For example, if a student gets 76.76% on their test; then their mark is considered a 'conditional pass' as it falls between the ranges of 75%-85%

I need excel to determine which range their test results fall under and then automatically enter the text result in the cell underneath, e.g.:

Name: Student Percentage total: 76.76% Result: Conditional Pass

I'm going to have to assign every possible number (including decimal points) to one of three text results.

Hope this makes some sense. Thanks!

Upvotes: 1

Views: 4945

Answers (1)

HarveyFrench
HarveyFrench

Reputation: 4568

Jeepeds answer is a great way to do this, so I'll help you a bit more.

Jeeped specified a table of values in his answer using {0,0.5,0.75,0.85,0.95} which will work, but it means the values are in every formula of every cell. The way below allows you to maintain a separate list of values that define the result text associated with a range of percentages

I would set up a a range with a table of values as follows. 

LowerMark       UpperMark          ResultText
0.0             0.50             Fail
0.50            0.60             Ok Pass
0.60            0.75             Contional Pass
0.75            0.85             Good Pass
0.85            0.90             Excellent
0.90            1                Brilliant
1               9999             This should never happen

Lets say the above table is in sheet2 in cells A1 to C7. Note that the upper mark column is not really needed, but it helps "read the table". (It could be set using a formula that gets it's value form the next rows LowerMark)

In you main list that has the pupil marks, the cell that you want to display the "Result Text" will need a formula like the following. This formula refers to cell D6 which is the cell that has the numeric percentage they obtained.

 =VLOOKUP('Sheet2'!$A$1:$C$7, D6, 3, TRUE)

Note that the table of values assumes that you are calculating and storing the percentage for each child as a number that ranges from 0 to 1 (not 0 to 100)

Upvotes: 1

Related Questions