Sorting Numerical data..

**Hodged** · 11-06-2006, 05:39 AM

Hi there guys,
I was wondering if anyone could give me any help. I'm trying to sort a column of data containing percentages into groups and then subtotal these groups.

Basically, how would I sort the data below into groups 10-20%, 20-30%,30-40%, 40%+, without doing it manually?

Sales Contribution %
41.4 %
39.1 %
31.7 %
28.9 %
7.1 %
16.6 %
22.7 %
30.0 %
36.2 %
29.3 %
36.6 %
25.2 %
54.1 %
30.0 %
45.0 %
31.2 %
27.4 %
36.5 %
38.6 %
30.0 %

Thanks!
Dave.

**mangesh_yadav** · 11-06-2006, 05:51 AM

Check out the FREQUENCY function.

Mangesh

**mangesh_yadav** · 11-06-2006, 06:05 AM

Sorry for the incomplete info:

Try the following:

Lets say your data is in column A (A1:A20)

In column C, enter as follows:
C1 = 0%
C2 = 10%
C3 = 20%
C4 = 30%
C5 = 40%

In column D enter as follows
D1 =SUMPRODUCT(--($A$1:$A$20<C2),--($A$1:$A$20>=C1),$A$1:$A$20)
D2 =SUMPRODUCT(--($A$1:$A$20<C3),--($A$1:$A$20>=C2),$A$1:$A$20)
D3 =SUMPRODUCT(--($A$1:$A$20<C4),--($A$1:$A$20>=C3),$A$1:$A$20)
D4 =SUMPRODUCT(--($A$1:$A$20<C5),--($A$1:$A$20>=C4),$A$1:$A$20)
D5 =SUMPRODUCT(--($A$1:$A$20>=C5),$A$1:$A$20)

Note that cells D1:D4 have the same formula, so you can enter the formula in D1 and copy down till D4. D5 has a different formula