Encontrar la última celda no vacía de una fila con un conjunto particular de valores

Question

Tengo una hoja de cálculo de datos de estudiantes, donde cada fila es un solo estudiante. Para cualquier clase determinada, hay varias columnas 1-N, donde N es la mayor cantidad de veces que cualquiera de los estudiantes ha repetido el curso. Entonces, algunos datos de ejemplo, donde un curso se ha repetido como máximo 4 veces, con lo que quiero calculado en la columna E:

+---+-------+-------+-------+-------+-----------+
|   |   A   |   B   |   C   |   D   |     E     |
+---+-------+-------+-------+-------+-----------+
| 1 | 225-1 | 225-2 | 225-3 | 225-4 | 225-final |
| 2 | F     | EP    | C     |       | C         |
| 3 | A     |       |       |       | A         |
| 4 | W     | D     | W     | F     | F         |
| 5 | EP    | C     |       |       | C         |
+---+-------+-------+-------+-------+-----------+

Ya descubrí la lookupfunción, que parece acercarme a lo que quiero. Entonces, por ejemplo, LOOKUP("X",A2:D2)obtendré la columna no vacía más alta (es decir, la más reciente en el alfabeto) para un estudiante determinado. Dados los datos anteriores, obtendría un resultado como el siguiente:

+---+-------+-------+-------+-------+-----------+
|   |   A   |   B   |   C   |   D   |     E     |
+---+-------+-------+-------+-------+-----------+
| 1 | 225-1 | 225-2 | 225-3 | 225-4 | 225-final |
| 2 | F     | EP    | C     |       | F         |
| 3 | A     |       |       |       | A         |
| 4 | W     | D     | W     | F     | W         |
| 5 | EP    | C     |       |       | EP        |
+---+-------+-------+-------+-------+-----------+

Los problemas con esto son los siguientes:

1. En lugar del valor de columna más alto que no esté vacío, en realidad quiero el más bajo. Por lo tanto, los estudiantes que obtienen una D, F, W, EP o EF pueden reemplazar la clase y (con suerte) obtendrán mejores resultados en un intento futuro. Quiero capturar el intento más reciente que no sea W, EP o EF (consulte a continuación). Tenga en cuenta que esta no siempre es una mejor calificación: hemos tenido estudiantes que obtuvieron una D, reemplazaron la calificación y luego obtuvieron una F. Me gustaría capturar la F.

2. Una cuestión que complica esto es que los estudiantes pueden recibir una calificación de EP o EF, que no debería incluirse, y terminar reemplazando las calificaciones con letras más altas dado mi enfoque actual, y reemplazaría una F si pudiera revertirlo lookup; esto indica que una El estudiante tuvo un retiro de emergencia ya sea aprobando o reprobando el curso. Creo que podría reemplazar todos los valores EP y EF con WP o WF para simplificar las cosas. Si hacer esto afecta su respuesta, ¡tenga en cuenta eso!

Answer 1

Según su primera matriz, parece que está interesado en la última celda no vacía de esa fila. Con la condición no deben ser iguales a EP, EFni W. ¿Es esta una suposición correcta? Si es así, utilice a continuación:

Fórmula en E2:

=INDEX(A2:D2,,AGGREGATE(14,3,(A2:D2<>"")*(A2:D2<>"W")*(A2:D2<>"EP")*(A2:D2<>"EF")*(COLUMN(A2:D2)),1))

Arrastre hacia abajo....

Si esto no es lo que desea, ¿puede decirme cuáles son las restricciones? Por el momento, a veces explicas que el último valor es lo que buscas y al final estableces restricciones.

EDITAR:

¿Cómo funciona esto?

AGGREGATEobtendrá el número más alto (según el parámetro 14) devuelto por la ecuación (A2:D2<>"")*(A2:D2<>"W")*(A2:D2<>"EP")*(A2:D2<>"EF")*(COLUMN(A2:D2), que devolverá una matriz de unos y ceros basada en todas las reglas especificadas, multiplicada por su número de columna respectivo. El resultado del número de columna más alto será el valor de calificación.

Luego utilizará este número más grande como parámetro de columna en la INDEXfunción.