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
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]

20​

[/td][td]

1​

[/td][/tr]

[tr][td]

2​

[/td][td]

50​

[/td][td]

1​

[/td][/tr]

[tr][td]

3​

[/td][td]

80​

[/td][td]

2​

[/td][/tr]

[tr][td]

4​

[/td][td]

110​

[/td][td]

3​

[/td][/tr]

[tr][td]

5​

[/td][td]

140​

[/td][td]

3​

[/td][/tr]

[tr][td]

6​

[/td][td]

170​

[/td][td]

4​

[/td][/tr]

[tr][td]

7​

[/td][td]

200​

[/td][td]

4​

[/td][/tr]

[tr][td]

8​

[/td][td]

230​

[/td][td]

5​

[/td][/tr]

[tr][td]

9​

[/td][td]

260​

[/td][td]

6​

[/td][/tr]

[tr][td]

10​

[/td][td]

290​

[/td][td]

6​

[/td][/tr]

[tr][td]

11​

[/td][td]

320​

[/td][td]

7​

[/td][/tr]

[tr][td]

12​

[/td][td]

350​

[/td][td]

7​

[/td][/tr]

[tr][td]

13​

[/td][td]

380​

[/td][td]

8​

[/td][/tr]

[tr][td]

14​

[/td][td]

410​

[/td][td]

9​

[/td][/tr]
[/table]

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...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]

20​

[/td][td]

0-49​

[/td][/tr]

[tr][td]

2​

[/td][td]

50​

[/td][td]

0-49​

[/td][/tr]

[tr][td]

3​

[/td][td]

80​

[/td][td]

50-99​

[/td][/tr]

[tr][td]

4​

[/td][td]

110​

[/td][td]

100-149​

[/td][/tr]

[tr][td]

5​

[/td][td]

140​

[/td][td]

100-149​

[/td][/tr]

[tr][td]

6​

[/td][td]

170​

[/td][td]

150-199​

[/td][/tr]

[tr][td]

7​

[/td][td]

200​

[/td][td]

150-199​

[/td][/tr]

[tr][td]

8​

[/td][td]

230​

[/td][td]

200-249​

[/td][/tr]

[tr][td]

9​

[/td][td]

260​

[/td][td]

250-299​

[/td][/tr]

[tr][td]

10​

[/td][td]

290​

[/td][td]

250-299​

[/td][/tr]

[tr][td]

11​

[/td][td]

320​

[/td][td]

300-349​

[/td][/tr]

[tr][td]

12​

[/td][td]

350​

[/td][td]

300-349​

[/td][/tr]

[tr][td]

13​

[/td][td]

380​

[/td][td]

350-399​

[/td][/tr]

[tr][td]

14​

[/td][td]

410​

[/td][td]

400-449​

[/td][/tr]
[/table]

Create a new table looking likethis...
[Table="width:, class:grid"][tr][td] [/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]

1​

[/td][td]0-49[/td][/tr]

[tr][td]

2​

[/td][td]

2​

[/td][td]50-99[/td][/tr]

[tr][td]

3​

[/td][td]

3​

[/td][td]100-149[/td][/tr]

[tr][td]

4​

[/td][td]

4​

[/td][td]150-199[/td][/tr]

[tr][td]

5​

[/td][td]

5​

[/td][td]200-249[/td][/tr]

[tr][td]

6​

[/td][td]

6​

[/td][td]250-299[/td][/tr]

[tr][td]

7​

[/td][td]

7​

[/td][td]300-349[/td][/tr]

[tr][td]

8​

[/td][td]

8​

[/td][td]350-399[/td][/tr]

[tr][td]

9​

[/td][td]

9​

[/td][td]400-449[/td][/tr]
[/table]

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.