Can't sort values in new categories!

JonOlsen

New Member
Joined
Nov 30, 2013
Messages
4
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
Joined
Nov 15, 2013
Messages
524
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
Joined
Feb 16, 2013
Messages
6,723
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
Joined
Nov 30, 2013
Messages
4
Thanks alot! That worked perfectly :)

Now I can go to sleep without worries :p haha
 

JonOlsen

New Member
Joined
Nov 30, 2013
Messages
4

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
Joined
Feb 16, 2013
Messages
6,723
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
Joined
Nov 30, 2013
Messages
4

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
Joined
Feb 16, 2013
Messages
6,723
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
Joined
Dec 5, 2013
Messages
1
Thanks for sharing information,your information are very helpful for me and increase my knowledge.
 

Forum statistics

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

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