# Can't sort values in new categories!

#### JonOlsen

##### New Member
Hey!

I'm working on a project in PowePivot were I have a database with different roads and there average length in km.
The range in length goes from about 20 km to 230 km.
What I want is to sort these lengths into 5 groups;
0-50 km , 50-100 km, 100-150 km, 150-200 km and 200-250 km.

I've probably broke the world record for IF functions trying to get this to work. Here is my best try:

=IF([length])<=50;"0-50";IF(50<[length]>=100;"50-100";IF(100<[length]>=150;"100-150";IF(150<[length]>=200;"150-200";"200-250"))))

This function will categorize the roads from 0-50 , but it puts the rest of the roads as 200-250 which is incorrect.

Can anybody help me with my excisting function? or give me a tip on another way or function to sort my problem?

All comments will be appreciated

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### jsotola

##### Well-known Member
Code:
``````=IF( comparison,  true,  false                                                 )
=IF( comparison,  true,  IF( comparison, true,  false                         ))
=IF( comparison,  true,  IF( comparison, true,  IF( comparison, true,  false )))

=IF(E8<50, "0-50", IF(E8<100, "50-100", IF(E8<150, "100-150", IF(E8<200, "150-200", IF(E8<250, "200-250", ">250")))))``````

Last edited:

#### FDibbins

##### Well-known Member
If you want to use IF's, you need to start with the highest range 1st, and work down to the lowest. However, try using this...
=ROUNDUP(A1/50,0)

It will take a value and categorise it as 1, 2, 3 etc
 A​ B​ 1​ 20​ 1​ 2​ 50​ 1​ 3​ 80​ 2​ 4​ 110​ 3​ 5​ 140​ 3​ 6​ 170​ 4​ 7​ 200​ 4​ 8​ 230​ 5​ 9​ 260​ 6​ 10​ 290​ 6​ 11​ 320​ 7​ 12​ 350​ 7​ 13​ 380​ 8​ 14​ 410​ 9​

You can then use those categories as needed

#### JonOlsen

##### New Member
Thanks alot! That worked perfectly

Now I can go to sleep without worries haha

#### JonOlsen

##### New Member

ADVERTISEMENT

Hey that was a clever soloution! Really liked that one.
But is it a easy way to name the groups instead of numbering them 1,2,3 etc?
I will look into it myself, but you obviously have a greater knowledge than me

Thank you for your answering!

#### FDibbins

##### Well-known Member
If there is a reason you need to use a "word" instead, we could probably use a lookup table to do the conversion

#### JonOlsen

##### New Member

ADVERTISEMENT

Yeah, my goal is to group the roads in to categories and then put the categories in a slicer in a Pivot Table. So they dont say 1,2,3 etc but 0-50, 50-100, 100-150 etc.
I've tried a couple of times but cant seem to work at first eyesight. Do I have to make a new table in PowerPivot with the names to and then "lookup" at that table?

#### FDibbins

##### Well-known Member
OK a slight variation on my suggestion.

1st table...
 A​ B​ 1​ 20​ 0-49​ 2​ 50​ 0-49​ 3​ 80​ 50-99​ 4​ 110​ 100-149​ 5​ 140​ 100-149​ 6​ 170​ 150-199​ 7​ 200​ 150-199​ 8​ 230​ 200-249​ 9​ 260​ 250-299​ 10​ 290​ 250-299​ 11​ 320​ 300-349​ 12​ 350​ 300-349​ 13​ 380​ 350-399​ 14​ 410​ 400-449​

Create a new table looking likethis...
 D​ E​ 1​ 1​ 0-49 2​ 2​ 50-99 3​ 3​ 100-149 4​ 4​ 150-199 5​ 5​ 200-249 6​ 6​ 250-299 7​ 7​ 300-349 8​ 8​ 350-399 9​ 9​ 400-449

Then change the formula in B1, down, to this...
=VLOOKUP(ROUNDUP(A1/50,0),\$D\$1:\$E\$9,2,0)

#### haydennathan

##### New Member
Thanks for sharing information,your information are very helpful for me and increase my knowledge.

Replies
6
Views
1K
Replies
1
Views
315
Replies
1
Views
54
Replies
45
Views
867
Replies
2
Views
453

Threads
1,136,434
Messages
5,675,843
Members
419,586
Latest member
RoteichA

### 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

### 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