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

user8421

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

 

gzmorgan0

@user8421 ,

 

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"