Foro Excel

Hola a todos:
Tengo un problema con la fórmula de promedio entre fechas, se trata de que en una celda, aparezca el plazo medio de solución de problemas (en dí­as) de una sección cerrados en el mes de enero, febrero, etc. bueno en el anexo lo explico un poco mejor.
Gracias por adelantado Lima

Hola,
respetando la forma de tu tabla, definimos algunos nombre paraa los rangos (para que la fórmula sea un poco más legible):
cerrado =Hoja2!$C$3:$C$19
dias =Hoja2!$D$3:$D$19
sector =Hoja2!$A$3:$A$19

y la fórmula serí­a la siguiente
SUMAPRODUCTO((MES(cerrado)=1)*((sector)=31)*(dias))/SUMAPRODUCTO((MES(cerrado)=1)*NO(ESBLANCO(cerrado))*((sector)=31))
donde debes reemplazar el valor para el mes y para el sector.

El NO(ESBLANCO(cerrado)) es para evitar que SUMAPRODUCTO tome en cuenta los blancos en el rango de Mes Cerrado como valor 1.

El problema de esta fórmula es que si el denominador es 0, da error. Una forma de evitar esto es condicionar la fórmula con SI

=SI(SUMAPRODUCTO((MES(cerrado)=1)*NO(ESBLANCO(cerrado))*((sector)=31))=0,"",SUMAPRODUCTO((MES(cerrado)=1)*((sector)=31)*(dias))/SUMAPRODUCTO((MES(cerrado)=1)*NO(ESBLANCO(cerrado))*((sector)=31)))

Puedes fijarte en el archivo adjunto (Hoja2)

Estimados colegas:
Este tema parece recurrente, y la solución del amigo Jordun va perfecta. He hecho una variante, por si en lugar de "cero" en las casillas de mes en que no se ha terminado un trabajo se quiere poner un "" (celda vací­a).
Un saludo, y felicidades por la aportación, Jordun

Hola a todos:
Muchas gracias a los dos, sois unos genios, la formula funciona perfectamente.
Una pregunta si sois tan amables y no os doy mucho la lata, quiero aprovechar esta fórmula para otro trabajo, solo que cuando la paso a la otra hoja, no se si hago mal algún paso a la hora de definir un nombre o al introducir la formula, el caso es que no me funciona.
Me podí­ais explicar que pasos tengo que seguir para definir los nombre para los rangos y como se introduce la formula (no he trabajo nunca con nombre para los rangos)
Muchas gracias por adelantado
Un saludo Lima

Estimado pinoarna:
=SI(SUMAPRODUCTO(--(sector=$F3);--(MES(cerrado)=G$2);--(dias<>""))=0;"";+SUMAPRODUCTO(--(sector=$F3);--(MES(cerrado)=G$2);dias)/SUMAPRODUCTO(--(sector=$F3);--(MES(cerrado)=G$2);--(dias<>"")))

=SI( ... establece una condición, que consiste en que si no hay "sector" en el 'mes cerrado', entonces, no se moleste en hacer nada más.
En caso contrario, vemos:
+SUMAPRODUCTO(--(sector=$F3);--(MES(cerrado)=G$2);dias)/SUMAPRODUCTO(--(sector=$F3);--(MES(cerrado)=G$2);--(dias<>""))
SUMAPRODUCTO es una función que trabaja con matrices.
El formato es SUMAPRODUCTO(condición 1;condición2;...condición "n"[opcional: rango a operar])
Aquí­ tenemos dos condiciones: el 'sector' -que puede ser señalado como rango o su nombre, como en este caso- debe coincidir con el que aparece en F3, y el 'mes cerrado' debe coincidir con el de G2. (Los "$" ahorran trabajo, porque mantienen la fila o columna estables, al copiar hacia abajo y a la derecha la primera de las celdas con fórmula).
En nuestro caso, usamos
SUMAPRODUCTO(--(sector=$F3);--(MES(cerrado)=G$2);dias)
Para que consulte si se cumple las condiciones y, al final, suma los dí­as que las cumplen, en el rango de dí­as. Los rangos deben ser de la misma dimensión (Nº de filas). Opera con columnas; en otro caso, su uso es más complejo.
Los signos "--" se usan para que SUMAPRODUCTO use cadenas de "1" y "0", si se cumple o no la condición, en lugar de "VERDADERO"/"FALSO", con los que no se puede operar.
Al hacer el cálculo, SUMAPRODUCTO tendrá parejas de datos tipo "1/0" "0/0" "0/1" y "1/1". Sólo aceptará las "1/1", que son las cumplen las dos condiciones, para operarlas con los datos de "número de dí­as", que se corresponden con eso y al final, sumará los productos "1*Nº dí­as", que es lo suyo.
Se supone que con eso, SUMAPRODUCTO ha sumado los dí­as de las series de datos que cumplen las dos condiciones. Como un promedio supone un dato dividido por otro dato, se usa
/SUMAPRODUCTO(--(sector=$F3);--(MES(cerrado)=G$2);--(dias<>"")
En este caso, las condiciones son que el sector coincida con el de F3, que el 'mes cerrado' de la serie de fechas coincida con la de G2... y que el número de dí­as que corresponda no esté vací­o. Con eso, ahora, SUMAPRODUCTO tendrá series de tipo "000" "001"..."111", y sólo operará con estas últimas, y "sumaproducteará" el resultado de multiplicar unos por unos y sumarlos. Eso equivale a un recuento de cuántas veces se cumple las tres condiciones.
Al final, el número de datos a sumar dividido entre las veces que se da el suceso, nos da el promedio.
Si se comprende el uso de SUMAPRODUCTO, la alpicación a ortos casos es más sencilla.
Espero que te sirva, y perdona el "misal"....

Estimado xpelos:
Después de todo esto, ya no me quedan palabras, decirte que eres un genio es poco, y he aprendido la lección (creo), de verdad mil gracias por todo y aplicaré todo lo que me has enseñado.
Un saludo Lima

Estimado pinoarna:
Sólo una matización... Creo que, en mi caso, se puede aplicar la historia del loro que tiene aprendida alguna lección, y asombra al público con la lección aprendida. El genio es quien le enseñó la lección, y no exactamente el loro...
Gracias por tus ánimos, que de vez en cuando, se agradece alguna mentirijilla piadosa...
Hasta pronto !!!