Using array formulas on cell ranges, and extending in two directions

0
votes

I have a spreadsheet that tracks participants in a series of ongoing studies. One sheet (Data) is where I manually drop in the names of participants in each study. The other sheet (Study History) should populate automatically with each participant's history and total studies.

Each week, new participants will be added to Column A in Study History as the group grows, and new studies will be added to the Data sheet.

I would like to use an array formula (or something else if it would work better) to BOTH extend the formulas in columns B-G of Study History DOWN infinitely, to incorporate newly added participants, AS WELL AS extend the formulas in columns E-G to the RIGHT, to incorporate new studies. However, due to the fact that the formulas contain ranges already (e.g. in COUNTIF functions), I'm not able to make the array formulas function properly. I also can't figure out how to extend an array in two directions, but maybe I'm being dim.

Spreadsheet here.

1
arraysgoogle-sheetsspreadsheetarray-formulas
Welcome to SO. The way you are asking the question makes it a bit hard to help you, because essentially there is nothing specific to be done/helped with. Can you post what you've tried? - lhcgeneva
So, regarding extending the COUNTIF cells down, B8 is currently =COUNTIF(E8:8,"Unmoderated") Typically for an array formula, I would make it =ARRAYFORMULA(COUNTIF(E8:8,"Unmoderated")), however it already has the range E8:8 in the original formula, so it doesn't work. Not sure how to apply a range to a range. - Matt

1 Answers

0
votes

Your objective can be achieved in many ways, the key concern would always be about volatility.

For example: A Dynamic Defined Name can be used to obtain the data range from the sheet DATA (defined as =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))). Yet the use of the OFFSET function makes the Dynamic Defined Name volatile and by extension all the formulas in sheet Study History pointing to it.

This solution proposed the use of the INDEX function, in order to avoid volatility:

Assuming the data range in sheet DATA starts at A1 and it’s a continuous range of data (i.e. no blank rows nor blank columns in between)

Enter this formula in E1 then copy to E2:E3 and till last column with records

= T( Data!A1 )

Enter this formula in E4 then copy till last column with records

= IF( E$1 = "", "",
COUNTIF( E$8  : INDEX(E:E, 1 + ROW( E$7 ) + COUNTA($A:$A ) - COUNTA( A$1:A$7 ) ), E$3 ) )

Enter this formula in E5 then copy till last column with records

= IF( E$1 = "", "", IF( E$5 = "", "Enter Invited", E$4 / E$5 ) )

Enter this formula in B8 then copy to C8 and till last row with records

=COUNTIF( $E8 : INDEX(8:8, - 1 + COLUMN( $E8 ) + COUNTA( Data!$1:$1 ) ), B$7 )

Enter this formula in D5 then copy till last row with records

=SUM( $B8:$C8 )

Enter this formula in E8 then copy till last row and column with records

=IF( OR( E$1 = "", $A8 = "" ), "",
IFERROR( CHOOSE( 1 + COUNTIF( Data!A:A, $A8 ), "", E$3 ), "!Err" ) )