Subscribe Bookmark RSS Feed

Summary Question

Hegedus

Community Trekker

Joined:

Jun 23, 2011

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?

1 REPLY
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

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