Buscar en Hoja EC!

=SI.ERROR(
INDICE(EC!$E$2:$E$1000,
AGREGAR(15,6,
FILA(EC!$B$2:$B$1000)-FILA(EC!$B$2)+1/((EC!$B$2:$B$1000=$B10)*(EC!$D$2:$D$1000=2)
),1)
),SI($O10<6,5,0)
)

Buscar en Aula.xlsx

=SI.ERROR(
INDICE('/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$E$2:$E$5000,
AGREGAR(15,6,
FILA('/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$A$2:$A$5000)-FILA('/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$A$2)+1/(('/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$A$2:$A$5000=$B10)*('/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$D$2:$D$5000=17)
),1)
),SI($O10<6,5,0)
)

Buscar mayor

=LET(
v1,SI.ERROR(
INDICE(EC!$E$2:$E$1000,
AGREGAR(15,6,
(FILA(EC!$B$2:$B$1000)-FILA(EC!$B$2)+1)/((EC!$B$2:$B$1000=$B10)*(EC!$D$2:$D$1000=3))
,1)),""),
v2,SI.ERROR(
INDICE('/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$E$2:$E$5000,
AGREGAR(15,6,
(FILA('/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$A$2:$A$5000)-FILA('/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$A$2)+1)/(('/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$A$2:$A$5000=$B10)*('/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$D$2:$D$5000=17))
,1)),""),
SI(Y(v1="",v2=""),SI($O10<6,5,0),MAX(SI(v1="",-9E+99,v1),SI(v2="",-9E+99,v2)))
)

Buscar mayor SIN LET

=SI(
Y(
ESERROR(INDICE(EC!$E$2:$E$1000,AGREGAR(15,6,(FILA(EC!$B$2:$B$1000)-FILA(EC!$B$2)+1)/((EC!$B$2:$B$1000=$B10)*(EC!$D$2:$D$1000=3)),1))),
ESERROR(INDICE('/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$E$2:$E$5000,AGREGAR(15,6,(FILA('/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$A$2:$A$5000)-FILA('/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$A$2)+1)/(('/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$A$2:$A$5000=$B10)*('/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$D$2:$D$5000=17)),1)))
),
SI($O10<6,5,0),
MAX(
SI.ERROR(INDICE(EC!$E$2:$E$1000,AGREGAR(15,6,(FILA(EC!$B$2:$B$1000)-FILA(EC!$B$2)+1)/((EC!$B$2:$B$1000=$B10)*(EC!$D$2:$D$1000=3)),1)),-9E+99),
SI.ERROR(INDICE('/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$E$2:$E$5000,AGREGAR(15,6,(FILA('/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$A$2:$A$5000)-FILA('/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$A$2)+1)/(('/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$A$2:$A$5000=$B10)*('/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$D$2:$D$5000=17)),1)),-9E+99)
)
)

Promedio

=SI.ERROR(PROMEDIO(SI(('/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$A:$A=$B10)*ESNUMERO(COINCIDIR('/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$D:$D,{18,19,20,21,22},0)),'/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$E:$E)),SI($O10<6,5,0))

Promedio con redondeo

=SI.ERROR(
    REDONDEAR.MAS(
        PROMEDIO(
            SI(
                (
                    '/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$A:$A = $B10
                )
                *
                ESNUMERO(
                    COINCIDIR(
                        '/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$D:$D,
                        {18,19,20,21,22},
                        0
                    )
                ),
                '/Volumes/KINGSTON/3er parcial/Bitácora PU/[Aula.xlsx]Registros'!$E:$E
            )
        ),
        0
    ),
    SI(
        $O10 < 6,
        5,
        0
    )
)

Contenido