Range issue in excel

[ashokbioinfo]

Hi,


I want to convert following range value to formula but getting error. can anybody help me;


· very common 10%,

· common 1% to < 10%;

· uncommon 0.1% to < 1%;

· rare 0.01% to < 0.1%;

· very rare < 0.01%.


=IF(M10<"0.01%", "very rare" , IF(AND(M5>=0.01%, M5<0.1%), "rare", IF(AND(M5>=0.1%, M5<1%), "uncommon", IF(AND(M5>=1%, M5<10%),


"common", IF(M10>="10%", "very common" )))))

 

[NARAYANK991]

Hi Ashok ,


An easy way is to set up a table as follows :

[pre]

0%	very rare
0.01%	rare
0.10%	uncommon
1%	common
10%	very common

[/pre]
Say this table is from A1:B5. In another cell , suppose you will enter the percentage value ; assume this cell is A7.


In A8 , enter this formula :


=INDEX(B1:B5,MATCH(A7,A1:A5,1))


This will return , in A8 , the text ( very rare , rare ,..., very common ) depending on what percentage has been entered in A7.


Narayan

 

[Luke M]

Taking Narayan's table, you can go even simpler with:

=LOOKUP(A7,A1:B5)