¿Cómo calcular el promedio ponderado en Excel?

Supongamos que tienes una lista de compras como se muestra en la captura de pantalla a continuación. Aunque la función PROMEDIO de Excel te daría el precio medio sin tener en cuenta el peso o la cantidad, un enfoque más preciso en estos casos es calcular el promedio ponderado. Esto refleja mejor el costo real por unidad al dar mayor influencia a los artículos con mayores pesos o frecuencias en el resultado final.

Para calcular el precio promedio ponderado, utiliza una combinación de las funciones SUMAPRODUCTO y SUMA de la siguiente manera:

Selecciona una celda en blanco, como F2, e introduce la siguiente fórmula:

=SUMPRODUCT(C2:C18,D2:D18)/SUM(C2:C18)

y presiona la tecla Intro para obtener el resultado.

Nota: En esta fórmula, C2:C18 se refiere a la columna Peso, y D2:D18 se refiere a la columna Precio. Ajusta estos rangos según sea necesario para tu propio diseño de datos. La función SUMAPRODUCTO multiplica cada peso por el precio correspondiente y suma los resultados, mientras que SUMA totaliza los pesos, produciendo el promedio ponderado correcto. Asegúrate de usar rangos de igual longitud y de que no haya celdas desiguales o vacías en tus datos, ya que esto podría provocar errores de cálculo.

Si el promedio ponderado calculado muestra demasiados o muy pocos decimales para tu preferencia, selecciona la celda, luego haz clic en el botón Aumentar Decimales botón o Disminuir Decimales botón en la Inicio pestaña para ajustar los decimales mostrados según sea necesario.

Si encuentras un error como #¡VALOR!, verifica que cada celda referenciada contenga un valor numérico y que los rangos sean consistentes. Además, evita incluir cualquier fila de encabezado en tu rango de cálculo para asegurar resultados precisos. Al trabajar con conjuntos de datos más grandes, considera usar rangos con nombres para mayor claridad y facilidad de mantenimiento.

La fórmula anterior calcula el precio promedio ponderado para todos los artículos. En análisis prácticos, podrías querer el promedio ponderado para categorías específicas, como encontrar el precio promedio ponderado solo para Manzanas. En tales casos, puedes mejorar la fórmula para incluir una condición basada en tus criterios.

Para hacer esto, selecciona una celda en blanco, como F8, e introduce la siguiente fórmula:

=SUMPRODUCT((B2:B18="Apple")*C2:C18*D2:D18)/SUMIF(B2:B18,"Apple",C2:C18)

Luego presiona la tecla Intro para calcular el promedio ponderado que cumple con tus criterios específicos. Esta fórmula multiplica cada par de peso y precio solo si el artículo coincide con la condición (“Manzana” en este caso), los suma y divide por la suma de los pesos solo para ese artículo.

Nota: Aquí, B2:B18 es la columna Fruta, C2:C18 es el Peso y D2:D18 es el Precio. Reemplaza “Manzana” por otro artículo según sea necesario. Este método funciona bien para filtrar por una condición; si necesitas filtrar por varios criterios (por ejemplo, tipo de fruta y proveedor), puede ser necesario una columna auxiliar o una fórmula más avanzada.

Después de aplicar la fórmula, podrías querer ajustar los decimales para mayor claridad. Selecciona la celda de resultado y usa los botones Aumentar Decimales o Disminuir Decimales en la Inicio pestaña para cambiar los decimales mostrados.

Si la fórmula devuelve un resultado inesperado, confirma que los criterios tienen coincidencias dentro de tu rango objetivo y ten cuidado con celdas vacías o entradas de texto en columnas destinadas a ser numéricas.

En algunas situaciones, es posible que necesites calcular promedios ponderados sobre rangos que cambian de tamaño, contienen valores faltantes o requieren filtrado flexible, como aplicar varios criterios a la vez. En lugar de actualizar manualmente fórmulas o rangos, automatizar el cálculo con una macro VBA puede ahorrarte tiempo y reducir las posibilidades de errores, especialmente útil cuando se trata de grandes conjuntos de datos o aquellos que se actualizan regularmente.

Aquí está cómo crear y usar una macro VBA para promedios ponderados:

1. Haz clic en Desarrollador > Visual Basic (o presiona Alt + F11) para abrir la ventana del editor de Microsoft Visual Basic para Aplicaciones. Luego, haz clic en Insertar > Módulo, y pega el siguiente código en la nueva ventana de módulo:

Sub WeightedAverageVBA()
    Dim rngCriteria As Range
    Dim rngWeight As Range
    Dim rngValue As Range
    Dim criteriaStr As String
    Dim totalWeighted As Double
    Dim totalWeight As Double
    Dim i As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rngCriteria = Application.InputBox("Select the range for criteria (optional, press Cancel to skip):", xTitleId, Type:=8)
    criteriaStr = Application.InputBox("Enter criteria for filtering (leave blank for all):", xTitleId, Type:=2)
    Set rngWeight = Application.InputBox("Select the Weight (numeric) range:", xTitleId, Type:=8)
    Set rngValue = Application.InputBox("Select the Value (e.g. Price) range:", xTitleId, Type:=8)
    
    totalWeighted = 0
    totalWeight = 0
    
    If rngCriteria Is Nothing Or criteriaStr = "" Then
        For i = 1 To rngWeight.Cells.Count
            If IsNumeric(rngWeight.Cells(i).Value) And IsNumeric(rngValue.Cells(i).Value) Then
                totalWeighted = totalWeighted + rngWeight.Cells(i).Value * rngValue.Cells(i).Value
                totalWeight = totalWeight + rngWeight.Cells(i).Value
            End If
        Next i
    Else
        For i = 1 To rngWeight.Cells.Count
            If rngCriteria.Cells(i).Value = criteriaStr Then
                If IsNumeric(rngWeight.Cells(i).Value) And IsNumeric(rngValue.Cells(i).Value) Then
                    totalWeighted = totalWeighted + rngWeight.Cells(i).Value * rngValue.Cells(i).Value
                    totalWeight = totalWeight + rngWeight.Cells(i).Value
                End If
            End If
        Next i
    End If
    
    If totalWeight = 0 Then
        MsgBox "Weighted average cannot be calculated: total weight is zero.", vbExclamation, xTitleId
    Else
        MsgBox "Weighted average: " & totalWeighted / totalWeight, vbInformation, xTitleId
    End If
End Sub

2. Presiona F5 (o haz clic en el Ejecutar botón) para ejecutar.
Se te pedirá que selecciones los rangos paso a paso (rango de criterios —esto puede omitirse si no es necesario, rango de pesos y rango de valores). También puedes ingresar criterios específicos para filtrar tu cálculo o dejarlo en blanco para considerar todos los datos. La macro soporta rangos de datos dinámicos, lo que la hace práctica si tu tabla crece o cambia regularmente.

Finalmente, obtendrás un cuadro de mensaje que enumera el resultado del promedio ponderado.

Consejos:

• Este enfoque automatiza el análisis repetitivo del promedio ponderado y puede ampliarse para manejar opciones adicionales de filtrado u salida.
• Asegúrate de que los rangos seleccionados sean de igual longitud y que los tipos de datos sean consistentes.
• Incluye manejo básico de errores como se muestra (por ejemplo, en casos donde no se encuentran pesos válidos o la suma de pesos es cero).
• Si deseas aplicarlo solo a filas visibles/filtradas, puedes mejorar aún más el código con la enumeración especial de celdas.

Si encuentras problemas de permisos o seguridad de macros, asegúrate de que las macros estén habilitadas en la configuración de Excel antes de ejecutar el código.

Promedio de rango con redondeo en Excel

Tasa promedio de cambio en Excel

Calcular promedio/tasa de crecimiento anual compuesta en Excel

Promedio móvil/promedio rodante en Excel

Total acumulado/promedio en Excel