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))
Patrick2788's avatar
Patrick2788
Silver Contributor
Aug 23, 2022
It sounds like you don't have 365 and access to those functions. When you pressed enter the sheet re-calculated and the formula broke.
Kyle_P19's avatar
Kyle_P19
Copper Contributor
Aug 23, 2022

Patrick2788 , that is true, I do not have 365 yet. I didn’t even know it had formula functions the one-time purchase doesn’t.

If I sign up, am I able to copy and paste the formula, and adjust the ranges as needed?

The “fold down” part of re-writing the code is a new concept in excel for me.

Thank You, by the way. If this functionality can work smoothly for me, it’s a game changer.

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Aug 23, 2022

    Kyle_P19 

    Yes, you could adjust the named ranges to accommodate the actual data.

     

    If 365 is not an option, you can use OliverScheurich 's solution.

Resources