Distribution functions

[Guest]

Is there a function that will find the recurring values within a range and
then count the number of recurrences for those values? For example, if my
range 1,2,1,3,2,1, then 1 = 3 times, 2 = 2 times, and 3 = 1 time. I would
like the results returned so that they can be charted.

 

[Guest]

if data is in column a try
in B1
=countif($A$1:$A$1000,A1) and copy down to end of data. (Change range as
appropriate
You can then plot B vs A
or you could select column A, use <data><filters><advanced filter>
Select unique values in location c1
enter
in D1 use
=countif($A$1:$A$1000,C1) and copy down to end of data in C
Plot D vs C
You wont have overlapping data points in this plot.

 

[Mike Middleton]

Lowkey -

Is there a function that will find the recurring values within a range and
then count the number of recurrences for those values? For example, if my
range 1,2,1,3,2,1, then 1 = 3 times, 2 = 2 times, and 3 = 1 time. I would
like the results returned so that they can be charted. <

(A) COUNTIF function

(B) array-entered FREQUENCY function

(C) Analysis ToolPak Histogram tool (counts and chart)

(D) my Better Histogram add-in from www.treeplan.com

- Mike
www.mikemiddleton.com

 

[Gord Dibben]

Lowkey

You can use the frequency function.

With your data range of A1:A10.

Type in 1,2,3 in cells B1:B3.

Select C1:C3 and copy or type this into the active cell.

=FREQUENCY(A1:A10,B1:B3)

This is an array formula so press ctrl+shift+enter.


Gord Dibben Excel MVP