Extracting a list from an array

[PT]

I have a large rectangular table filled with cells each holding
numerical integer values, from -2,000 to + 2,000.

I want to extract a listing sorted by values, ending up with a two
column list data in the form of:

Value1, frequency1
Value2, frequency2

Etc…

Is there a statistical or other Ifunction which will accomplish this

 

[T. Valko]

Try this...

With your data in the range A1:E5...

You want the new list starting in cells A10:B10.

Enter this formula in A10:

=SMALL(A$1:E$5,ROWS(A$10:A10))

Enter this formula in B10:

=COUNTIF(A$1:E$5,A10)

Select both A10 and B10 and copy down until you get errors (meaning all the
numbers have been extracted). Delete the formulas that return the errors.

--
Biff
Microsoft Excel MVP


I have a large rectangular table filled with cells each holding
numerical integer values, from -2,000 to + 2,000.

I want to extract a listing sorted by values, ending up with a two
column list data in the form of:

Value1, frequency1
Value2, frequency2

Etc…

Is there a statistical or other Ifunction which will accomplish this