Numerical range

[vidhate0123]

Hi,

I want formula in excel

Value is 1 to 5 I want answer "A"
if value more than 5 to 16 I want answer " B"
if value more than 16 to 30 I want answer " c"
if value more than 30 to 50 I want answer "D"
if value more than 50 to 100 I want answer " E"
if value more than 100 to 150 I want answer "F"
if value more than 150 to 200 I want answer "G"
if value more than 200 to 250 I want answer "H"

if value more than 250 to 300 I want answer "I"

if value more than 350 to 400 I want answer "J"

if value more than 400 to 450 I want answer "K"

 

[S.H.A.D.O.]

Hi vidhate0123,

Does this work for you?

=IF(A1="","",IF(A1<=5,"A",IF(A1<=16,"B",IF(A1<=20,"C",IF(A1<=50,"D",IF(A1<=100,"E",IF(A1<=150,"F",IF(A1<=200,"G",IF(A1<=250,"H",IF(A1<=300,"I",IF(A1<=400,"J",IF(A1<=450,"K",IF(A1>450,"More than 450 entered.")))))))))))))

I hope this helps!

 

[Aladin Akyurek]

Try...

=LOOKUP(value,{0,1,5,16,30,50,100,150,200,250,300,350,400},{"","A","B","C","D","E","F","G","H","I","J","K","L"})

 

[S.H.A.D.O.]

Aladin,

Very nice.
I knew there was an easier way but I just couldn't think of it!

 

[pella88]

In cases there are 3+ conditions, if is not best option as it has to have a lot of nesting... My suggestion would be following: create a helping table and use vlookup in order to get the value you need...

Here is an example of such solution: https://www.dropbox.com/s/b9pcqcgvffm9i5d/BP_Example_Vlookup_True.xlsx?dl=0

 

[vidhate0123]

Yes ... it working perfectly, Thanks

 

[vidhate0123]

Yes ... it working perfectly, Thanks