Forum Discussion

Kyle_P19's avatar
Kyle_P19
Copper Contributor
Aug 21, 2022
Solved

Rather Complicated Array Analysis

Hello, I would like to take the names listed in a column range, and search for them by row, in a separate array area. For every row that included every name in the list, I would like to average a n...
excel
Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Aug 22, 2022

    Kyle_P19 

    This will do it.

     

    First, we pair down the study group to eliminate blanks:

    =DROP(study,-COUNTBLANK(study))

     Next, determine which rows have all matches.  XMATCH is run against the row where unique values are found so a name appearing twice won't cause a false positive.

    =LET(b,BYROW(tests,LAMBDA(row,(IF(COUNT(XMATCH(sg,UNIQUE(row,1)))=ROWS(sg),ROW(row)-11,"")))),FILTER(b,b<>""))

    Fold it into a small formula:

    =AVERAGE(CHOOSEROWS(means,PickRows))
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Aug 23, 2022

Kyle_P19 

=SUMPRODUCT(IF(C12<>"",COUNTIF(OFFSET($E$12:$H$12,ROW($1:$5)-1,0),C12)>0,TRUE)*IF(C13<>"",COUNTIF(OFFSET($E$12:$H$12,ROW($1:$5)-1,0),C13)>0,TRUE)*IF(C14<>"",COUNTIF(OFFSET($E$12:$H$12,ROW($1:$5)-1,0),C14)>0,TRUE)*IF(C15<>"",COUNTIF(OFFSET($E$12:$H$12,ROW($1:$5)-1,0),C15)>0,TRUE)*IF(C16<>"",COUNTIF(OFFSET($E$12:$H$12,ROW($1:$5)-1,0),C16)>0,TRUE),J12:J16)/SUMPRODUCT(IF(C12<>"",COUNTIF(OFFSET($E$12:$H$12,ROW($1:$5)-1,0),C12)>0,TRUE)*IF(C13<>"",COUNTIF(OFFSET($E$12:$H$12,ROW($1:$5)-1,0),C13)>0,TRUE)*IF(C14<>"",COUNTIF(OFFSET($E$12:$H$12,ROW($1:$5)-1,0),C14)>0,TRUE)*IF(C15<>"",COUNTIF(OFFSET($E$12:$H$12,ROW($1:$5)-1,0),C15)>0,TRUE)*IF(C16<>"",COUNTIF(OFFSET($E$12:$H$12,ROW($1:$5)-1,0),C16)>0,TRUE))

You can try this formula which returns the expected results in my sheet. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

 

What is your expected result if there are 5 different names in the study group? The formula returns an error because 5 different names from the study group can never appear in 4 passed test cells for one day. With IFERROR you can return an individual message instead of an error message in this case.

 

Another expected error is returned if the names in the study group aren't found in any single row of the passed test matrix.

 

 

  • Kyle_P19's avatar
    Kyle_P19
    Copper Contributor
    Aug 24, 2022
    Thank you both, options and information are always welcome. This is my first time using some of these functions, and there's some great info to learn from here.

    So, the number of options in the study group, will always match the number of columns in the passed test section. As for the different name errors, I am using data validation to limit selections.
    That should take care of the two errors.

Resources