Values represented by String

[spsafe]

Dear all,

I have a column (A) with numerical values ranging from 1-100 and I want to create another column (B) which will have respective labels for different ranges of the values in column (A). For example, I want values in column A between 51-60 to take the label "Alpha" in column (B) and values between 61-70 to take the label "Beta", etc.

Can you please help?

Pavlos

 

[dafan]

Make a table somewhere in your workbook with 1, 11, 21 etc.
Use a VLOOKUP to look up the value (eg 54):
=VLOOKUP(A1,Sheet2!A1:B3,2,1)

In this case I made in sheet2 three cells in column A {1,11,21}, and 3 cells in column B {Alpha, Beta, Gamma}.
In sheet1 I put the formula above in B1, the value to look up in A1.

6 -> Alpha,
14 -> Beta
etc

HTH.

 

[BGY23]

Create a lookup table with the values in one column I.e. 1 to 100 and in the right hand adjacent column put you description i.e. Alpha etc

Now on your original list do a vlookup based on the table you just created.

Advantages
You can update your lookup table with changes in descriptions and it will flow through to your data automatically.

New descriptions can be added and agian it will update your report.

Alternatively if you only have a few names you can write an if statement for ranges i.e.

Assume your data starts at A1

If(and(A51=>1,A1=<60),"Alpha",If(and(A61=>1,A1=<70),"Beta",0))

Problem is you can only have 7 options in a nested if statement. Any changes will mean you have to rewrite the whole formula.

Good luck

 

[BGY23]

****ed the formula up sorry..........

If(and(A1=>51,A1=<60),"Alpha",If(and(A1=>61,A1=<70),"Beta",0))

 

[Mike Blackman]

Hi,

No need for a Lookup table on this, ammend this with all the buckets you wish to create, I have added one for 60's and 70's;

=LOOKUP(A1,{0,"Other";60,"Alpha";70,"Beta";80,"Other"})

 

[spsafe]

Thank you all guys.

I have followed BGY23's suggestion and it works fine. I am pretty sure that should I use any of the other suggestions I would also be successful.

Best,

Pavlos