Contar.Si con multiples escenarios y fechas
Publicado por adolfo (18 intervenciones) el 14/10/2021 21:56:13
Saludos amigos, por aca en busca de orientación sobre una formula de Excel 2016 que me ha resultado muy complicada y me ha desbordado, doy detalles y adjunto archivo
Premisas:
- Se establece un rango, digamos, por ejemplo, A2:B1000, en ese rango se identificarán 2 columnas, una registrará los datos de fecha (A) y otra un valor alfabético (B) este valor será: A, B o L. Solo esos 3
- El rango A2:B1000 se segmentará en sub rangos de "N" filas este caso se tomarán 14, es decir, el primer rango de operación seria A10:B23
- El segmento de 14 filas del rango será dinámico, es decir, que el segundo rango de operación seria A24:B37, el tercero A25:B38 y así sucesivamente y la formula se aplicara por cada fila del segmento
- En el segmento se calculara la ocurrencia de cada uno de los valores A, B y L. Es decir, cuantas veces ocurrió A, cuantas B y cuantas L
- La fórmula buscara determinar:
1) Si alguna de las sumatoria de A o B (L no interesa) es igual o mayor que 3
2) Cumplida la condición 1, determinará cual de las dos sumatorias entre A y B es mayor
A modo de ejemplo: si de la aplicación de la condición 1 resulta que el valor A tiene una sumatoria de 4 ocurrencias (>3) y el valor B tiene una sumatoria de ocurrencias mayor a 4, se da por cumplida; no así cuando A tiene una sumatoria de 3 o más ocurrencias y B 3 o menos o cuando ambas sumatorias estén igualadas; sean 3 o más.
Hasta acá no presento problemas, tengo una formulación que resuelve las dos condiciones. El problema es con la No. 3
3) Se tienen dos sumatorias, una con un mínimo de 3 ocurrencias y la otra con más de 3. Pueden ser 5 y 8, 10 y 20, por ejemplo, pero la menor de las dos debe tener un mínimo de 3 ocurrencias y la segunda una cantidad mayor.
La fórmula debe buscar la última fecha de ocurrencia la sumatoria menor, y contar todas las ocurrencias de las otras opciones (L incluida), si la otra opción diferente a L es mayor, dará por valido el resultado.
Supongamos que en el segmento de 14 filas hay 4 A, 6 B y 4 L; la formula ya sabe que como mínimo hay 3 A y que hay más B que A, ahora la formula tendría que asegurarse que después de la última fecha de ocurrencia de A, solo ocurrieron B o L, no necesariamente las 6 B de todo el rango tienen que ocurrir con posterioridad a la última fecha de ocurrencia de A.
La fórmula se copiaría en cada fila a partir de D23
Se han identificado 18 resultados posibles, pudiendo existir más, pero no se ha considerado relevante, porque básicamente solo importa enunciar 2 (A y B) y todo lo demás que no sean estos, serán L
A modo ilustrativo, en la hoja Ejemplo los rangos coloreados y en la columna D se muestran algunos resultados esperados y en la E cual es la condición asociada a ese resultado
Premisas:
- Se establece un rango, digamos, por ejemplo, A2:B1000, en ese rango se identificarán 2 columnas, una registrará los datos de fecha (A) y otra un valor alfabético (B) este valor será: A, B o L. Solo esos 3
- El rango A2:B1000 se segmentará en sub rangos de "N" filas este caso se tomarán 14, es decir, el primer rango de operación seria A10:B23
- El segmento de 14 filas del rango será dinámico, es decir, que el segundo rango de operación seria A24:B37, el tercero A25:B38 y así sucesivamente y la formula se aplicara por cada fila del segmento
- En el segmento se calculara la ocurrencia de cada uno de los valores A, B y L. Es decir, cuantas veces ocurrió A, cuantas B y cuantas L
- La fórmula buscara determinar:
1) Si alguna de las sumatoria de A o B (L no interesa) es igual o mayor que 3
2) Cumplida la condición 1, determinará cual de las dos sumatorias entre A y B es mayor
A modo de ejemplo: si de la aplicación de la condición 1 resulta que el valor A tiene una sumatoria de 4 ocurrencias (>3) y el valor B tiene una sumatoria de ocurrencias mayor a 4, se da por cumplida; no así cuando A tiene una sumatoria de 3 o más ocurrencias y B 3 o menos o cuando ambas sumatorias estén igualadas; sean 3 o más.
Hasta acá no presento problemas, tengo una formulación que resuelve las dos condiciones. El problema es con la No. 3
3) Se tienen dos sumatorias, una con un mínimo de 3 ocurrencias y la otra con más de 3. Pueden ser 5 y 8, 10 y 20, por ejemplo, pero la menor de las dos debe tener un mínimo de 3 ocurrencias y la segunda una cantidad mayor.
La fórmula debe buscar la última fecha de ocurrencia la sumatoria menor, y contar todas las ocurrencias de las otras opciones (L incluida), si la otra opción diferente a L es mayor, dará por valido el resultado.
Supongamos que en el segmento de 14 filas hay 4 A, 6 B y 4 L; la formula ya sabe que como mínimo hay 3 A y que hay más B que A, ahora la formula tendría que asegurarse que después de la última fecha de ocurrencia de A, solo ocurrieron B o L, no necesariamente las 6 B de todo el rango tienen que ocurrir con posterioridad a la última fecha de ocurrencia de A.
La fórmula se copiaría en cada fila a partir de D23
Se han identificado 18 resultados posibles, pudiendo existir más, pero no se ha considerado relevante, porque básicamente solo importa enunciar 2 (A y B) y todo lo demás que no sean estos, serán L
A modo ilustrativo, en la hoja Ejemplo los rangos coloreados y en la columna D se muestran algunos resultados esperados y en la E cual es la condición asociada a ese resultado
- 01.zip(24,5 KB)
Valora esta pregunta
0