Aug 21 2022 10:51 AM
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 numerical value listed down another column, but only in the rows which match the initial list of names.
I have included a screenshot, of a small file showing what I am trying to accomplish, with some more description.
Any Help is Greatly Appreciated,
Kyle P.
Aug 21 2022 07:47 PM
Aug 22 2022 06:08 AM - edited Aug 22 2022 06:11 AM
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.
Aug 22 2022 04:15 PM
SolutionThis 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))
Aug 22 2022 06:29 PM
Aug 23 2022 01:15 AM
Aug 23 2022 02:13 AM
@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.
Aug 23 2022 02:42 AM
=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.
Aug 23 2022 06:09 AM
Yes, you could adjust the named ranges to accommodate the actual data.
If 365 is not an option, you can use @OliverScheurich 's solution.
Aug 23 2022 05:47 PM
Aug 24 2022 05:57 PM
@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.
Aug 25 2022 08:49 AM
Aug 25 2022 03:32 PM - edited Aug 26 2022 01:48 AM
So I signed in and reset again, and it is sort of working now. But as seen in the attached file, it does not compute the average properly after names are added or taken away from the Passed Test cells. The ranges seem good though.
Aug 22 2022 04:15 PM
SolutionThis 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))