Función PROMEDIO.SI.CONJUNTO: un ejemplo.

En un correo un lector realizaba una consulta sobre la función PROMEDIO.SI.CONJUNTO, y la forma de replicarla en versiones anteriores a Excel 2007:

...Me gustaría que me pudierais solucionar una duda que tengo sobre la función promedio.si.conjunto. En principio he descubierto que dichafunción no es posible para versiones de excel más antiguas al 2007. Por lo que me gustaría saber como puedo programar dicha función para versiones antiguas. Os paso la fórmula en concreto y si ésta se puede programar para versión 2000...

Efectivamente existen algunas nuevas funciones en Excel 2007 que no existen en versiones anteriores, en general, son funciones que nos permiten agrupar campos por distintos criterios; son funciones del estilo CONTAR.SI.CONJUNTO o SUMAR.SI.CONJUNTO.
En esta ocasión hablaremos de la función
=PROMEDIO.SI.CONJUNTO(rango valores; rango criterios1; criterio1; rango criterios2; criterio2; ...)
función que nos devolverá el promedio del rango de valores, es decir, la media aritmética, de todas las celdas que cumplan los múltiples criterios asignados.
Plantearé la siguiente tabla de datos a modo de ejemplo:

En esta tabla tenemos los datos de distintos hoteles de una localidad, definida de acuerdo a la Cadena de hoteles al que pertenece cada uno de ellos y la categoría por estrellas, viendo cuántas habitaciones tiene cada hotel.

Nos interesa conocer cuál es el promedio de los hoteles de la cadena Alfa; así como el promedio de los hoteles Alfa de 3 estrellas.
Para desarrollar esta tarea propondré tres posibilidades:

• Función PROMEDIO.SI.CONJUNTO:
  Sobre la tabla de datos de la imagen anterior ejecutamos la siguiente función
  

  

  
  donde obtenemos un resultado de 158,75 es decir, la media aritmética de las habitaciones de los hoteles 'Alfa' es de 158,75. De igual forma, pero agregando a la función un nuevo criterio obtendríamos el promedio de los hoteles 'Alfa' de 3 estrellas:
  =PROMEDIO.SI.CONJUNTO($C$2:$C$13;$A$2:$A$13;"Alfa";$B$2:$B$13;"3x")
  esto es, una media de 205 habitaciones. Es decir, de los elementos o registros a estudio calculamos el promedio sólo de aquellos que cumplen ambos criterios a un tiempo; i.e., es un promedio condicional múltiple
• Con Tabla dinámica:
  Ya hemos trabajado en ocasiones anteriores con tablas dinámicas, y sabemos que generando una tabla dinámica podremos llegar a los mismos resultados del punto anterior de manera sencilla. Construimos nuestra tabla dinámica con la siguiente estructura:
  

  

  
  es decir, en el área de columnas disponemos el campo 'Cadena-hotel', en el área de datos el campo 'Habitaciones' resumido por promedio y en el área de páginas el campo 'Categoría'; ajustamos los elementos para que sólo nos muestre los hoteles 'Alfa' de categoría '3x' o de todas las categorías.
  

  

• Empleando funciones Matriciales:
  especialmente útiles cuando no dispongamos de la versión Excel 2007.
  Para calcular el promedio de habitaciones de los hoteles 'Alfa' escribiremos y ejecutaremos en forma matricial:
  {=PROMEDIO(SI($A$2:$A$13="Alfa";$C$2:$C$13))}
  igualmente, para obtener el segundo promedio, ampliaremos esta misma hasta
  {=PROMEDIO(SI($A$2:$A$13="Alfa";SI($B$2:$B$13="3x";$C$2:$C$13)))}