BookmarkSubscribe
Choose Language Hide Translation Bar

Summary Question

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 Community Manager

Re: Summary Question

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.

-Jeff