1

Is there a way in Excel to, after providing a list of groups and a column of discrete values, map a group label to each discrete value? How can I tell Excel to essentially check which group the value falls into and append the correct label? For example, if I have a list of ages:

23
24
24
25
32
33
36
37
41
44
45
63
67

I might want to say that there are groups 20-29,30-39,40-49,50+ so that we then have:

23  20-29
24  20-29
24  20-29
25  20-29
32  30-39
33  30-39
36  30-39
37  30-39
41  40-49
44  40-49
45  40-49
63  50+
67  50+

I think it might make things easier if instead of trying to append these labels I just call 20-29 1, 30-39 2 and so on. Then I just need an IF statement that says append 2 if value is between 20-29 and so on.

| improve this question | | | | |
  • The answer is probably, but, can you show a before and after (screen shots, or typing it out) as I struggled to follow fully :( Hopefully a before and after will make it super clear so we can help :) – Dave Nov 18 '14 at 15:57
  • @Dave Makes sense, an example has been placed above. – Max Power Nov 18 '14 at 16:15
1

Set up an area somewhere in your workbook that looks something like this:

         X      Y
1       20    20-29
2       30    30-39
3       40    40-49
4       50    50+

Then, if your age values are in column A starting in row 2, enter

=VLOOKUP(A2, X$1:Y$4, 2)

into cell B2 (or wherever you want the group ID displayed) and drag it down.  The VLOOKUP searches the little X-Y lookup table for the last row where the X value is ≤ the A value.  The 2 parameter tells it to return the value from the second column of that lookup table.

| improve this answer | | | | |
  • The problem is while I can copy out all 60+ values and append the category to create a lookup table in this example, there might sometimes be 1000+ different values each of which fall in a different category. In this case a vlookup table is not feasible. How can one handle such cases? – Max Power Nov 18 '14 at 17:00
  • I don't understand. What are "categories"? Your question doesn't mention them. Are they the same as groups? It's hard for people to understand you if you change terminology and/or add conditions/constraints that weren't mentioned in the original question. You said that you have "a list of groups". Are you now saying that you have over 1000 groups? And that you don't have a list of them? And are you saying that, contrary to your example, the groups are not defined by consecutive ranges of numeric values? How is Excel supposed to be able to do the mapping? Please elaborate. – G-Man Says 'Reinstate Monica' Nov 18 '14 at 17:39
  • Sorry, categories are groups. As mentioned, I have a list of groups, but I have many values and would like to append a group label to each of them. The problem is in your example your X values are 20,30,40 and so on. In my example I have 23,24...,67 as possible values which I would like to map to a range (e.g. 20-29). The problem is that I could have significantly more than just 23,24..,67 as possible values. I could have 5,6,7....,7000 as possible values. Here your vlookup method would be cumbersome and so I am wondering if there is an alternative solution. – Max Power Nov 18 '14 at 18:40
  • You seem to be missing the point. I didn’t include 23, 24, etc., in my table because I didn’t need to. If A8 contains 36, then cell B8 will evaluate to 30-39 because the VLOOKUP finds that X2 (30) is ≤ A8 (36), but X3 (40) is > A8 (36), and so it returns Y2 (30-39). But if your groups aren’t defined by consecutive ranges of numeric values, then you’ve got a different question. – G-Man Says 'Reinstate Monica' Nov 18 '14 at 19:21
  • You're right, I was missing the point. So VLOOKUP tables don't require exact values to be in the table, it will automatically group it into the last table group where the value to be mapped is less than the VLOOKUP first column value? – Max Power Nov 18 '14 at 20:14
0

Try using FLOOR() and CEILING.

Let's say your first age to check is in cell A1 and you want to group all ages into bands of 10. FLOOR() rounds down to the nearest number you specify, while CEILING() rounds up.

Then simply put an IF() on the front to deal with your upper limit.

=IF(A1>=50,"50+",FLOOR(A1,10)&"-"&CEILING(A1,10)-1)
| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.