Summary Question

[#1]

Hi,

A would like to create a summary of some data:

Column 1 Has group IDs (~5000 groups with 11 members each)

Column B has the individual IDs for each member - creatively labelled 1-11)

Column III has a numeric value for each of the members

I would like to create an analysis that shows how many times each member (1-11) was either the minimum or maximum value of the group

For example of the 5000 groups

Member 1 was the maximum 1213 times of the 5000 groups

Member 2 was 1001 times

........

How would you do this most efficiently?

[#2]

You'll need to create a column that identifies a row as the minimum (or maximum) within the Group.

This formula will do that:

   Col Minimum(:Measure, :Group) == :Measure

Giving a 1 or 0 in each row. The 1s are the rows where that member was the minimum within Group.

Then you can use Tabulate to get a sum of this new column for each Member ID.

Tabulate

	Is Min of Group
Member ID	Sum
1	534
2	511
3	538
4	461
5	485
6	520
7	474
8	496
9	508
10	513
11	534

The attached data table shows the technique and has an attached script for the Tabulate.