Forum Discussion
Rather Complicated Array Analysis
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))
I am trying to have excel search for names in array C12:C16.
Then, based on those names, search the array G12:J16 (by ROW).
Any rows containing ALL of the names from C12:C16, would then have their corresponding test scores averaged.
Test scores are located in array M12:M16.
In the photo above, we would be looking for the average of 74 and 89, as those are the only two rows (in G12:J16) containing both “Rachel” and “Melissa” (the names found in C12:C16).
I am not sure if this can be done with formulas alone, we may need a helper column or two. I am just learning about the more complicated formulas, such as Index Matching, and Array Formulas, so I’m not sure what they can do with some good layout design.
Hopefully this description helps.
Thank You,
-Kyle P.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 I have upgraded to 365, and done the Un-install/re-install process. But the formula still says “#Name” when I add a name to the study group section (C12:C16).
It stays that way no matter what I do, including ctr+z.
