Medias ponderadas

Hoy os presento un artículo, que aunque no es estrictamente algo que podamos considerar como puramente de Excel, creo que le va a servir a muchos usuarios que utilizan esta popular hoja de cálculo. Se trata de obtener lo que se denomina media ponderada.

La Real Academia Española (RAE), define la media ponderada como el "resultado de multiplicar cada uno de los números de un conjunto por un valor particular llamado su peso, sumar las cantidades así obtenidas, y dividir esa suma por la suma de todos los pesos".

¿Está claro, verdad?. Jajajaja. Probablemente leído así a bote pronto, esa definición lo que hace es confundir más que aclarar, pero si lo vemos con un ejemplo, nos daremos cuenta que es algo bastante sencillo de entender.

Vamos a suponer que hoy día 26-01-2011, efectuamos dos inversiones, una que nos rinde el 7% de interés nominal anual, y cuyo vencimiento es el 31-12-2011, y otra que rinde el 1% de interés nominal anual, y cuyo vencimiento es dentro de 5 días, es decir, el 31-01-2011. En ambos casos, supondremos que los intereses nos los retribuyen al vencimiento de cada inversión.

Si pasáramos esta información a nuestra hoja de cálculo de Excel, nos quedaría algo así:


Si alguien nos preguntara, a qué tipo de interés medio nos retribuyen la inversión, podríamos contestarle dos cosas:

a) Al 4,00% de interés medio, pues esa cifra resulta de hacer la meda de tipos de interés: (7%+1%)/2
b) Nos faltan datos para poder dar una respuesta.

Si hemos elegido al respuesta a), sin duda estaremos cometiendo un grave error, porque entre otras cosas, las dos inversiones no tienen el mismo vencimiento. Piensa por un momento que la inversión 1 vence dentro de 339 días, es decir, todavía queda mucho, mientras que la inversión 2 está prácticamente finalizada, por lo que debemos valorar esta circunstancia, para calcular el tipo medio de interés de nuestras inversiones, pues en caso de no hacerlo, estaremos ponderando más de la cuenta el tipo de interés de la inversión 2 (la que está a punto de finalizar). Llegados a este punto, podríamos reformular nuestro esquema en Excel, de la siguiente forma:


Si nos volvieran a preguntar cual es el tipo de interés medio al que retribuyen nuestras inversiones, la respuesta que podríamos dar, sería una de estas:

a) Al 6,91% de interés medio, pues esa cifra resulta de multiplicar el 7% por los días que quedan hasta el vencimiento de esta primera inversión, es decir 339 días, multiplicando nuevamente el 1% por los días que quedan hasta el vencimiento de esta segunda inversión, es decir, 5 días, y dividiendo todo ello entre la suma de 339 y 5. Es decir, ese tipo medio del 6,91% lo obtendríamos de la siguiente fórmula:


b) Nos faltan datos para poder dar una respuesta.

Como en el caso anterior, si hubiésemos dado como respuesta la a), estaríamos cometiendo un error, pues nos falta el último dato, el importe de cada una de las inversiones (la inversión 1, y la inversión 2), y que es condición necesaria para poder determinar el tipo de interés medio al cual nos retribuyen esas inversiones. En ningún momento hemos dicho que ambas inversiones sean del mismo importe, por eso presuponer ese dato sería un error.

Vamos a suponer que la inversión 1 es de 10.000 euros, y que la inversión 2 es de 5.000 euros. Reformulemos por tanto nuestro esquema en Excel, para tenerlo todo más claro, de un vistazo:


Si ahora nos hicieran la preguntita de marras, nuestra respuesta sería que el tipo de interés medio resultante de estas inversiones, es del 6,96%, pues lo obtenemos de resolver la siguiente fórmula:


En realidad, lo que estamos haciendo, es ponderar (sopesar, o medir de forma proporcional) cada tipo de interés, con respecto al importe de cada una de las inversiones, y con respecto a la fecha de cada uno de los vencimientos. En términos matemáticos, lo que hemos hecho no es más que ponderar la media, o como reza el título de este artículo, calcular la media ponderada.

Si volvemos a mirar esta última fórmula donde obtenemos ese 6,96% de interés medio (ponderado), y releemos nuevamente la definición que da la RAE sobre la media ponderada, estoy seguro que ahora tendremos las cosas mucho más claras, y habremos entendido esta definición.

Como colofón de este artículo, vamos a suponer ahora que tenemos 20 inversiones, en lugar de 2, como era el caso que contemplábamos en el ejemplo. En ese caso, en lugar de hacer una fórmula donde el numerador y el denominador van a ser larguísimos, podemos solucionarlo muy fácilmente con el uso de la función SUMAPRODUCTO. Vamos a verlo con un ejemplo, donde podemos contemplar en la imagen, la fórmula que aparece en la barra de fórmulas:


Podemos construir dos columnas auxiliares, pare ver que efectivamente, la función SUMAPRODUCTO nos arroja el mismo resultado, que una fórmula tradicional:


En realidad, la fórmula SUMAPRODUCTO, lo que va a hacer es multiplicar cada elemento de las matrices que le indiquemos, sumando cada una de las líneas, de tal forma que si por ejemplo ponemos esto:

=SUMAPRODUCTO(C5:C24;E5:E24;F5:F24)

...le estaremos diciendo que haga esto:

=(C5*E5*F5)+(C6*E6*F6)+(C7*E7*F7)+(C8*E8*F8)+(C9*E9*F9)+(C10*E10*F10)+(C11*E11*F11)+(C12*E12*F12)+(C13*E13*F13)+(C14*E14*F14)+(C15*E15*F15)+(C16*E16*F16)+(C17*E17*F17)+(C18*E18*F18)+(C19*E19*F19)+(C20*E20*F20)+(C21*E21*F21)+(C22*E22*F22)+(C23*E23*F23)+(C24*E24*F24)

Es decir, tanto la función SUMAPRODUCTO que hemos puesto, y esta otra fórmula tradicional tan larga que acabamos de poner, hacen exactamente lo mismo.

Espero que os haya sido útil este artículo, y que lo podáis aplicar alguna vez en vuestro trabajo. Como siempre, desde aquí podéis descargar el fichero de Excel, con el ejemplo final que hemos visto en este artículo.