Values represented by String

spsafe

New Member
Joined
Apr 2, 2009
Messages
2
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

dafan

Well-known Member
Joined
May 6, 2008
Messages
692
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

Well-known Member
Joined
Aug 13, 2008
Messages
515
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

Well-known Member
Joined
Aug 13, 2008
Messages
515
****ed the formula up sorry..........

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

Mike Blackman

Well-known Member
Joined
Jun 8, 2007
Messages
2,494
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

New Member
Joined
Apr 2, 2009
Messages
2
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,658
Messages
5,597,403
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top