Extract a list of names and averages from a table but observing certain attributes.

[edge37]

Hi, I'm trying to extract or filter, from the included table, a list of students from A2:A7 that have an average of >=91, from K2:K7, but only if the student's notes (from B2:J7) are >=80, meaning, If the student has an average of 92, but one of his HW grades is 75, then she will not be in the list. I want to put that list in the table from M2:N7, Names in M column and their averages in N column.

Can you help me, please?

Thanks

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	NAME	HW1	HW2	HW3	HW4	HW5	HW6	HW7	HW8	HW9	AVERAGE		OUTSTANDING PERFORMANCE
2	Camila Alessandra Diaz Mayes	100	100	63	81	92	100	97	100	100	92.56
3	Ericka Sofia Castellanos Pineda	76	85	95	70	78	79	88	93	95	84.33
4	Eva Patricia Rodríguez Escoto	98	83	100	100	72	80	100	100	100	92.56
5	Hanna Valeria Mejía Pagoada	87	100	66	82	89	100	99	100	100	91.44
6	Julia Valeria Barahona Hernández	100	100	91	100	100	100	100	100	100	99.00
7	Lia Alessandra Chavez Valenzuela	100	79	90	82	97	95	100	93	85	91.22
Sheet1

Cell Formulas
Range	Formula
K2:K7	K2	=AVERAGE(B2:J2)

 

[Fluff]

How about

=LET(a,B2:J7,c,COLUMNS(a),f,FILTER(A2:K7,(K2:K7>=91)*(MMULT(--(a>=80),SEQUENCE(c,,,0))=c)),INDEX(f,SEQUENCE(ROWS(f)),{1,11}))

 

[edge37]

Beautiful, thank you very much!

 

[Sufiyan97]

Another option

=FILTER(A2:A7,((B2:B7>=81)*(C2:C7>=81)*(D2:D7>=81)*(E2:E7>=81)*(F2:F7>=81)*(G2:G7>=81)*(H2:H7>=81)*(I2:I7>=81)*(J2:J7>=81))*(K2:K7>=91),"Not Found")

 

[Fluff]

You're welcome & thanks for the feedback.

 

[edge37]

How about

=LET(a,B2:J7,c,COLUMNS(a),f,FILTER(A2:K7,(K2:K7>=91)*(MMULT(--(a>=80),SEQUENCE(c,,,0))=c)),INDEX(f,SEQUENCE(ROWS(f)),{1,11}))

Excuse mr Fluff, how would the formula change if the student should still have an average of >=91 but have HW grades BETWEEN 81 and 90? Thanks again

 

[edge37]

Another option

=FILTER(A2:A7,((B2:B7>=81)*(C2:C7>=81)*(D2:D7>=81)*(E2:E7>=81)*(F2:F7>=81)*(G2:G7>=81)*(H2:H7>=81)*(I2:I7>=81)*(J2:J7>=81))*(K2:K7>=91),"Not Found")

Thank you Sufiyan97, I tried your option, it gives me the names but the averages do not show.

 

[Fluff]

how would the formula change if the student should still have an average of >=91 but have HW grades BETWEEN 81 and 90?

That makes no sense. If the grades are between 81 & 90 then the average cannot be >= 91

 

[edge37]

@Fluff
My bad, sorry, here I go again:
In your previous formula I changed that students must have a minimum of 91 (instead of >=80 in the formula) in their grades, now I need another formula but this time the average should still be 91 but, in any of their grades, a note of 81 to 90 could be tolerated to be in the list. This new list must not include those already in the first list (with grades >=91. The first formula is for an OUTSTANDING performance list and this second one that I need is for a DISTINGUISHED performance list.
Thank you for your time

 

[Fluff]

How about

=LET(a,B2:J7,c,COLUMNS(a),f,FILTER(A2:K7,(K2:K7>=91)*(MMULT(--(a>=81),SEQUENCE(c,,,0))=c)*(MMULT(--(a>=91),SEQUENCE(c,,,0))<>c)),INDEX(f,SEQUENCE(ROWS(f)),{1,11}))