Jun 24 2022 07:36 AM - edited Jun 24 2022 08:33 AM
Saludos a todos: tengo esta formula: =SI.ERROR(PROMEDIO.SI.CONJUNTO(G8:G3000;$B8:$B3000;">="&$A6;$B$8:$B$3000;"<="&$B$6);0) que devuelve el valor promedio de un rango de acuerdo a un filtro de fechas, funciona bien, pero me gustaría que, adicionalmente, me mostrara solo las celdas visibles del rango, ya que puedo aplicar filtros a la hoja y reducir la cantidad de celdas visibles. Agradeceré todos los comentarios, gracias
Jun 24 2022 08:36 AM
SolutionIf you're on 365, this might work for you:
=MAX(MAP(B8:B3000,G8:G3000,LAMBDA(b,g,IF(AND(b>=$A$6,b<=$B$6,SUBTOTAL(102,b)=1),g,0))))
Jun 24 2022 08:54 AM
Jun 25 2022 01:52 AM
Hi @a7024782
Hi
If you're looking to calculate an average with conditions on hidden/visible rows the following should work on Excel 2016
in G3:
=IF(
SUMPRODUCT(SUBTOTAL(102,OFFSET(B8,ROW(B8:B3000)-ROW(B8),,1))) = ROWS(B8:B3000),
AVERAGEIFS(G8:G3000, B8:B3000,">="&A6, B8:B3000,"<="&B6),
SUMPRODUCT(
SUBTOTAL(102,OFFSET(B8,ROW(B8:B3000)-ROW(B8),,1)),
(B8:B3000 >= A6) * (B8:B3000 <= B6),
G8:G3000
)
/ SUMPRODUCT(
SUBTOTAL(102,OFFSET(B8,ROW(B8:B3000)-ROW(B8),,1)),
(B8:B3000 >= A6) * (B8:B3000 <= B6)
)
)
Sample available in the attached file with a Table (same Dates & Values) to check the result
Jun 25 2022 12:39 PM
Jun 25 2022 09:09 PM
Jun 26 2022 12:33 AM
Hi @Patrick2788
It seems @a7024782 was looking to compute an AVERAGE. Your LAMDBA is really nice but not sure it generates an AVG ;)
Jun 24 2022 08:36 AM
SolutionIf you're on 365, this might work for you:
=MAX(MAP(B8:B3000,G8:G3000,LAMBDA(b,g,IF(AND(b>=$A$6,b<=$B$6,SUBTOTAL(102,b)=1),g,0))))