topic Re: Summary Question in Discussions

Summary Question

Hegedus — Fri, 08 May 2015 23:36:05 GMT

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?

Jeff_Perkinson — Sat, 09 May 2015 00:25:12 GMT

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

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