Can't sort values in new categories!

[JonOlsen]

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

 

[jsotola]

=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")))))

 

[FDibbins]

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]

Thanks alot! That worked perfectly

Now I can go to sleep without worries haha

 

[JonOlsen]

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]

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

 

[JonOlsen]

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]

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]

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