Calculate Fail, Pass, Merit or Distinction based on Numerical Value

[haydencohen]

Hi,

I'm trying to calculate whether someone has received a fail, pass, merit or distinction based on the range between two values. The range is detailed in a separate worksheet. In one worksheet:

FAIL 49
PASS 50
MERIT 60
DISTINCTION 70

In the main worksheet there is a figure, let's say 52. This would be a PASS. Is there a way for the cell to state PASS as 52 is between the ranges without using a horrendously long IF function?

I feel like something with VLOOKUP or INDEX-MATCH may do the trick, but it's proving complicated

All help would be much appreciated,


Hayden

 

[Jonmo1]

Welcome to the board..
IF that range detail is speareted into 2 columns, say A and B
A2 = Fail
B2 = 49
A3 = Pass
B3 = 50
etc..

Although the FAIL value shold probably be 0, if you're saying anything less than 50 is FAIL.

You could do
=LOOKUP(D2,B$2:B$5,A$2:A$5)

D2 is the value to rank..

 

[FryGirl]

Try

=LOOKUP(A1,{0,50,60,70},{"Fail","Pass","Merit","Distinction"})

 

[haydencohen]

Well that was quick and works perfectly. Thanks so much!

 

[Jonmo1]

You're welcome.