Este breve tutorial explica cómo hacer una fórmula de Excel para la varianza entre dos celdas.
En Microsoft Excel, hay 6 funciones diferentes para calcular la varianza. Sin embargo, ninguna de esas funciones es adecuada para encontrar la variación porcentual entre dos celdas. La razón es que las funciones incorporadas, como VAR, VAR.S, VAR.P, etc., están diseñadas para encontrar la varianza en el sentido clásico, es decir, qué tan lejos está un conjunto de valores de su promedio. Una variación porcentual es algo diferente. En este artículo, encontrará la fórmula correcta de porcentaje de varianza de Excel.
¿Cuál es la varianza porcentual?
A variación porcentualalias cambio porcentualdescribe un cambio proporcional entre dos números, un valor original y un nuevo valor.
los fórmula de varianza porcentual muestra cuánto cambia algo entre dos períodos. Por ejemplo, puede calcular la variación entre las ventas de este año y el año pasado, entre un pronóstico y la temperatura observada, entre un costo presupuestado y el real.
Cómo calcular el porcentaje de varianza en Excel
Hay dos fórmulas básicas para encontrar la variación porcentual en Excel.
Fórmula 1
Es la fórmula clásica de cambio porcentual.
(nuevo_valor – antiguo_valor) / antiguo_valor
fórmula 2
Una fórmula simplificada de porcentaje de varianza de Excel que devuelve el mismo resultado.
nuevo_valor / antiguo_valor – 1
Como ejemplo, busquemos una variación porcentual entre las ventas estimadas en B2 y las ventas reales en C2. Suponiendo que el número estimado es el valor «de referencia» y el real es un valor «nuevo», las fórmulas toman esta forma:
Fórmula 1:
=(C2-B2)/B2
Fórmula 2:
=C2/B2-1
De forma predeterminada, los resultados se muestran como números decimales. Para mostrar porcentajes, aplique el formato de número de porcentaje a las celdas de fórmula. Para ello, haga clic en el Estilo de porcentaje en el grupo Número en la pestaña Inicio o presione el atajo de formato de porcentaje Ctrl + Shift + %.
Cómo funciona la fórmula de varianza porcentual de Excel
Si tuviera que encontrar el cambio porcentual manualmente, tomaría un valor antiguo (original) y un valor nuevo, encontraría la diferencia entre ellos y lo dividiría por el valor original. Para obtener el resultado como porcentaje, lo multiplicarías por 100.
Por ejemplo, si el valor original es 160 y el nuevo valor es 120, el porcentaje de variación se puede calcular de esta manera:
=(120-160)/160
=-40/160
=-0.25
-0.25*100 = -0.25%
Cuando aplica el formato de número de porcentaje en Excel, un número decimal se muestra como un porcentaje automáticamente, por lo tanto, no necesita multiplicar por 100.
Prevenir errores #DIV/0
Si su conjunto de datos contiene algunos cero valores, envuelva las fórmulas en la función IFERROR para evitar errores de división por cero (#DIV/0!).
=IFERROR((C2-B2)/B2, 0)
=IFERROR(C2/B2-1, 0)
Calcular porcentaje de descuento
Las fórmulas de cambio porcentual son útiles para calcular un porcentaje de descuento. Entonces, señoras, cuando vayan de compras, recuerden esto:
discount % = (discounted price - regular price) / regular price
discount % = discounted price / regular price - 1
Las fórmulas muestran un porcentaje de descuento como un valor negativo porque el nuevo precio con descuento es menor que el precio normal anterior. Para mostrar el resultado como un numero positivo, envuelva las fórmulas en la función ABS. Por ejemplo:
=ABS((C2-B2)/B2)
Cómo calcular el porcentaje de varianza para números negativos
Si algunos de los valores originales están representados por números negativos, las fórmulas anteriores no funcionarán. Una solución común es hacer que el denominador de la primera fórmula sea positivo. Para ello, utilice la función ABS:
(nuevo_valor – antiguo_valor) / ABS(antiguo_valor)
Con el valor anterior en B2 y el nuevo valor en C2, la fórmula es la siguiente:
=(C2-B2)/ABS(B2)
Nota. Aunque este ajuste del ABS es técnicamente correcto, la fórmula puede producir resultados engañosos en caso de que el valor original sea negativo y el nuevo valor sea positivo, y viceversa.
Como ves, calcular el cambio porcentual en Excel es muy fácil. Para una experiencia práctica, le invitamos a descargar nuestro libro de trabajo de muestra a continuación. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!
(archivo .xlsx)