Which formula is best?

[jonnywakey]

Hi all

I'm stuck with the following problem.

I want to be able to change the number in a cell based on a percentage in another cell as follows:-

Cell A1 holds the dynamic number, Cell A2 holds the %age number.

Logic for cell A1 needs to be:- If A2 value <=65% then A1 shows number 5
If A2 value between 85% and 95% then A1 shows number 3
If A2 value >=95% then A1 shows number 1

Tried If And statements but couldn't get them to nest properly........any help appreciated.

Thanks

jonny

 

[Jonmo1]

You're missing a whole range, from 65% to 85%

I'll assume one of those is a typo, 85% - 95% should have been 65%-95%

Try

=LOOKUP(A2,{0,0.65,0.95},{5,3,1})

 

[Dave Patton]

I would use the relevant criteria in a Lookup but consider the following

=IF(A2>=0.95,1,IF(A2>=0.85,3,IF(A2>=0.65,4,5)))