Group list of data into 3 groups

[fighterpilot]

I have a list of data I'd like to group into 3 groups, top third, middle third and lower third. Can someone give me some help on how to do this?

Thanks...

--
Fighter Pilot
Manufacturing Engineer

 

[ACtrafficengr]

Have you tried using the RANK or PERCENTRANK function? The Format/Conditional format command can be used to color-code your data, too.

------------------------------------------
"...students of traffic are beginning to realize the false economy of mechanically controlled traffic, and hand work by trained officers will again prevail."

Wm. Phelps Eno, ca. 1928

 

[electricpete]

Yup. percentrank looks like it will work on unsorted arrays. Conditional forming based on a formula including percentrank would be a good way to designate items in an unsorted list.

I assume you know how to sort if that is desired?

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[zappedagain]

If you are looking for a manual way to do it you could do something like this (I'm assuming your data is in Column A)

in B1 enter a formula like this:

=IF(A1<(MAX(A:A)*1/3),"Bottom",IF(A1>(MAX(A:A)*(2/3)),"Top","Middle"))

Then copy this down the B column for all your data. The B Column will contain "Bottom", "Middle", or "Top". You can then sort (<Data><Sort>) the spreadsheet by Column B.

I hope that helps.

 

[fighterpilot]

Percentile function seemed to work. It looks like if I want to physically group the data I'd have to make a pivot table. I just used PERCENTILE(data list,0.33) and PERCENTILE(data list,0.66) to find the breaks and manually scanned the data after doing an ascending sort.

--
Fighter Pilot
Manufacturing Engineer