Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- Column Formula for summary value of group based on values in 2 other columns

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 3, 2020 2:11 PM
(500 views)

Hi everyone -

I'm looking for a column formula that would return the following desired column below. For simplicity in this post each group has 3 rows, but the dataset I currently have has groups with varying numbers of rows.

Any help appreciated!

Group | Time | Score | First Time Failed (Desired Column) |

A | 1 | Pass | 8 |

A | 3 | Pass | 8 |

A | 8 | Fail | 8 |

B | 1 | Pass | Pass |

B | 2 | Pass | Pass |

B | 6 | Pass | Pass |

C | 1 | Pass | 3 |

C | 3 | Fail | 3 |

C | 7 | Fail | 3 |

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

This is just a fun alternative that uses data table formulas. It will work for unequal Group sizes and if the table is not sorted.

The logic is to find the minimum row number for Fails in each Group. **Col Minimum(... , By)** is the key. Column statistic functions using the By option are very useful, but often overlooked functions.

Formula for column "Row First Failed"

Formula for column "Time First Failed". Note if the syntax seems strange, the right-hand formula is Time, then select **Subscript** from the **Row** functions and drag column 4, "Row First Failed"

2 REPLIES 2

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Column Formula for summary value of group based on values in 2 other columns

Here is a formula that works with your Example data table. It has not been tested beyond that, so you may need to make some adjustments

```
Names Default To Here( 1 );
dt = New Table( "Example",
Add Rows( 9 ),
New Column( "Group", Character, "Nominal", Set Values( {"A", "A", "A", "B", "B", "B", "C", "C", "C"} ) ),
New Column( "Time", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 3, 8, 1, 2, 6, 1, 3, 7] ) ),
New Column( "Score",
Character,
"Nominal",
Set Values( {"Pass", "Fail", "Pass", "Pass", "Pass", "Pass", "Pass", "Fail", "Fail"} )
)
);
dt << New Column( "First Time Failed",
character,
formula(
If( Row() == 1,
dt = Current Data Table();
check = :Score;
If( :Group == :Group[2],
Failed = Char( Col Max( :Time, :Group ), Failed = :Score )
);
,
If( :Group == Lag( :Group ),
If( :Score == "Fail",
check = "Fail"
);
Failed = Char( Col Max( :Time, :Group ) );
,
If( check == "Pass",
lagGroup = Lag( :Group );
:First Time Failed[dt << get rows where( :Group == lagGroup )] = "Pass";
Failed = Char( Col Max( :Time, :Group ) );
);
check = :Score;
)
);
failed;
)
);
```

Jim

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

This is just a fun alternative that uses data table formulas. It will work for unequal Group sizes and if the table is not sorted.

The logic is to find the minimum row number for Fails in each Group. **Col Minimum(... , By)** is the key. Column statistic functions using the By option are very useful, but often overlooked functions.

Formula for column "Row First Failed"

Formula for column "Time First Failed". Note if the syntax seems strange, the right-hand formula is Time, then select **Subscript** from the **Row** functions and drag column 4, "Row First Failed"