FÓRMULAS DE EXCEL

Aplicar probabilidad condicional a una tendencia acumulada

Aplicar probabilidad condicional a una tendencia acumulada

Funciones utilizadas: ALEATORIO.ENTRE, CONTAR, CONTARA, CONTAR.SI, SI.ERROR, SI, INDICE, COINCIDIR, VERDADERO, ESNUMERO, HALLAR

 

En el siguiente video se explica como podemos desarrollar una planilla que nos indique los desvíos positivos o negativos de un evento con N sucesos, basándonos en una tasa esperada de éxito por suceso y una tasa real de éxito por los N sucesos. En otras palabras y a modo práctico según se ejemplifica en el video, podremos comparar distintas tendencias para distintos bolilleros que contienen 9 bolillas del 1 al 9 cada uno basándonos en un evento de 15 sucesos por bolillero. Con estas tendencias podremos luego averiguar si es factible un desvío y por consecuente un desperfecto técnico tanto en el bolillero como en alguna o algunas de las bolillas, que afectan la igualdad de probabilidades para cada bolilla.

Dentro de la hoja y en el rango H2:P2 colocaremos los números que serán exitosos para cada suceso que realizaremos. Es importante que en este rango los valores numéricos se completen de izquierda a derecha sin dejar vacíos en el medio. No es importante el orden con lo cual podremos cargarlos por ejemplo como 4 – 2 – 7 – 5.

En el rango H3:P3 colocaremos el universo de números posibles que podemos obtener por cada suceso. En nuestro caso cada bolillero contiene bolillas del 1 al 9, con lo cual ingresaremos todos los números de esa serie en el rango.

Utilizaremos la columna A para contabilizar del 1 al 15 cada suceso por evento (bolillero) y la columna B para describir cada resultado efectivo por suceso. En ésta columna B podemos ir cargando cada resultado que obtuviéramos según la necesidad que tengamos, a modo didáctico nosotros lo definiremos con la función ALEATORIO.ENTRE asignándole un valor aleatorio entre todos los casos posibles que tenemos (9 bolillas con valores numéricos del 1 al 9).

En la columna C utilizaremos una fórmula de control que nos indica si cada suceso fue exitoso o no según los casos exitosos que hayamos definido previamente en el rango H2:P2. La sintaxis de la fórmula es la siguiente:

{=SI.ERROR(SI(B2=INDICE(posibles;COINCIDIR(VERDADERO;ESNUMERO(HALLAR(posibles;B2));0));SI(B2=INDICE(exitos;COINCIDIR(VERDADERO;ESNUMERO(HALLAR(exitos;B2));0));"EXITO";"FALLO");"ERROR");"ERROR")}

Para que ésta fórmula funcione deberemos nombrar al rango de números exitosos H2:P2 como «exitos» y al rango de números posibles H3:P3 como «posibles». Y nos devolverá una cadena de texto específica según el resultado de cada suceso donde «EXITO» significará que el suceso devolvió un número contenido dentro del rango de números exitosos, «FALLO» significará que devolvió un número que no se encuentra dentro del rango de números exitosos, y «ERROR» significará que aún no se evaluó el resultado del suceso (por ej. si aún no se cargó el resultado en la columna B).

En la columna D colocaremos una celda combinada que nos devolverá un único valor porcentual que consiste en la tasa esperada de éxito por cada suceso. Debido a que en nuestro ejemplo cada bolilla luego de cada suceso vuelve al bolillero, la probabilidad de que vuelva a salir cada bolilla se mantiene constante, por ende la tasa de éxito en cada suceso es la misma. Podemos definir a «Éxito» como el hecho de obtener cualquier valor como resultado que se encuentre dentro de los casos posibles de éxito. Por ejemplo, si todos los números del 1 al 9 los consideramos exitosos entonces tendremos una tasa de éxito del 100% en cada suceso, mientras que si consideramos exitosos a los números 1,2,3 y 4 nuestra tasa de éxito será del %44,4 en cada suceso (4 exitos sobre 9 posibles). La tasa esperada de éxito va a ser siempre el coeficiente entre la división de los casos exitosos y los casos posibles, y como mencionamos va a ser igual en todos los sucesos debido a que cada uno es independiente. Utilizaremos la función CONTAR aplicado a los rangos «exitos» y «posibles»para averiguar el número de casos exitosos y posibles respectivamente.

En la columna E colocaremos una celda combinada que nos devolverá un único valor porcentual, que consiste en la tasa real de éxito por los N sucesos que aplicaremos en nuestro evento. En otras palabras, nos informará de forma porcentual cuántos «éxitos» obtuvimos respecto al total de los sucesos realizados, es decir la efectividad real que obtuvimos en el evento. La fórmula que utilizaremos en esta celda será la siguiente:

=CONTAR.SI(C2:C16;"EXITO")/CONTARA(C2:C16)

El dividendo (parte superior) de la ecuación consistirá en la función CONTAR.SI la cual nos cuenta la cantidad de «EXITO» logrado en el rango de nuestra columna de control. El divisor (parte inferior) de la ecuación consistirá en la función CONTARA la cual nos cuenta la cantidad de celdas no vacías del rango de nuestra columna de control. Esta última función es una de las varias alternativas para definir el total de sucesos realizados. También se podría colocar directamente el número de sucesos, o sea 15 para nuestro ejemplo.

Finalmente en la celda E18 colocaremos un porcentual con la tendencia o desvío que obtendremos de restar la tasa esperada de éxito general menos la tasa real de éxito general. A modo de ejemplo, si todos los números posibles fueran éxitos o todos los números posibles no fueran éxitos, no habría desviación o tendencia ya que «lo que se esperaba» va a ser lo que «efectivamente sucedió». Sin embargo, cuando sólo algunos números son exitosos pueden existir desvíos. Éste número será porcentual positivo cuando logremos mayores éxitos de lo esperado por evento total, y será porcentual negativo cuando logremos menores éxitos de lo esperado por evento total.

CONCLUSIÓN

Se entiende que en condiciones normales y a infinitos sucesos, lo esperado y lo real deberían equilibrarse igualitariamente cuando cada opción de resultado tiene las mismas probabilidades.

Al considerar una muestra de N sucesos puede ocurrir que exista una diferencia (temporal) positiva o negativa en la cantidad de éxitos. Esta diferencia suele ser azar y se estabiliza en el tiempo, pero también puede ocurrir que haya un desperfecto técnico en el bolillero o en algunas bolillas que generan una tendencia o desvío real.

Consecuentemente, comparando tasas de tendencia para una misma cantidad de sucesos en distintos bolilleros, podemos inferir que una forma de acertar más números será elegir el bolillero con mayor márgen positivo de tendencia. Sin embargo no existe precisión absoluta en esta decisión ya que no sabemos si existe o no un desperfecto.

 

Te recomiendo ver el siguiente link con más videos de Fórmulas ingeniosas de Excel AQUÍ.

Salvo aclaración, todas las fórmulas y macros de este sitio están configuradas para aplicarse sobre la celda A1. Algunas fórmulas se encuentran encerradas entre llaves {} debido a que son fórmulas matriciales. Estas llaves no deben introducirse tecleándolas, sino que se generan automáticamente al aceptar la fórmula pulsando Control+Shift+Enter al mismo tiempo. Las fórmulas de este sitio son compatibles con versiones de Microsoft Excel® 2010 o superiores.