Calcular promedio de un rango dinamico

%3CLINGO-SUB%20id%3D%22lingo-sub-3472848%22%20slang%3D%22es-ES%22%3ECalculate%20average%20of%20a%20dynamic%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3472848%22%20slang%3D%22es-ES%22%3E%3CP%3EGreetings%20to%20all.%20I%20would%20greatly%20appreciate%20your%20help%20in%20resolving%20the%20following%20situation%3A%20I%20have%20a%20range%20of%20data%3B%20say%20A1%3AA100%20and%20in%20cell%20B1%20a%20random%20quantity%2C%20say%2010%3B%20what%20I%20would%20like%20is%20to%20calculate%20the%20average%20in%20the%20range%20A1%3AA100%2C%20depending%20on%20the%20amount%20specified%20in%20B1%2C%20in%20this%20case%2010%3B%20that%20is%2C%20I%20need%20to%20calculate%2C%20according%20to%20the%20above%20data%2C%20the%20average%20of%20A1%3AA10%2C%20but%20if%20in%20B1%20I%20put%2020%3B%20then%20the%20calculation%20would%20be%20for%20A1%3AA20.%20The%20closest%20I've%20come%20to%20the%20solution%20is%20this%3A%20%3DSI(ROWS(A1%3AA10)%26gt%3B%3DB1%3B%20AVERAGE(DESREF(A10%3B%3B%3B%20-B1))%3B%22%22)%2C%20but%20works%20limitedly.%3C%2FP%3E%3CP%3EI%20appreciate%20any%20comments.%20Thank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3472848%22%20slang%3D%22es-ES%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3472946%22%20slang%3D%22en-US%22%3ERe%3A%20Calcular%20promedio%20de%20un%20rango%20dinamico%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3472946%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1187812%22%20target%3D%22_blank%22%3E%40a7024782%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DAVERAGE(INDIRECT(%22A1%3AA%22%26amp%3BB1))%0A%3DPROMEDIO(INDIRECTO(%22A1%3AA%22%26amp%3BB1))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EYou%20can%20try%20this%20formula.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22average.JPG%22%20style%3D%22width%3A%20597px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22average.JPG%22%20style%3D%22width%3A%20597px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22average.JPG%22%20style%3D%22width%3A%20597px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22average.JPG%22%20style%3D%22width%3A%20597px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22average.JPG%22%20style%3D%22width%3A%20597px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22average.JPG%22%20style%3D%22width%3A%20597px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22average.JPG%22%20style%3D%22width%3A%20597px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F377879i66ED79B02D9773A7%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22average.JPG%22%20alt%3D%22average.JPG%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3473569%22%20slang%3D%22en-US%22%3ERe%3A%20Calcular%20promedio%20de%20un%20rango%20dinamico%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3473569%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1187812%22%20target%3D%22_blank%22%3E%40a7024782%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DAVERAGE(A1%3AINDEX(A%3AA%2CB1))%0A%3DPROMEDIO(A1%3AINDICE(A%3AA%3BB1))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3473570%22%20slang%3D%22es-ES%22%3ERe%3A%20Calculate%20average%20of%20a%20dynamic%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3473570%22%20slang%3D%22es-ES%22%3EThank%20you%20for%20your%20help%20in%20my%20case%20it%20has%20not%20worked%2C%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3473599%22%20slang%3D%22es-ES%22%3ERe%3A%20Calculate%20average%20of%20a%20dynamic%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3473599%22%20slang%3D%22es-ES%22%3EI%20think%20it%20didn't%20work%20because%20of%20the%20following%3A%20in%20the%20example%20sent%20for%20analysis%20mention%20the%20range%20A1%3AA100%3B%20being%20in%20A1%20the%20first%20value%20to%20average%20and%20so%20on%20up%20to%20cell%20A100.%20In%20that%20case%20it%20does%20work%3B%20but%20in%20the%20event%20that%20the%20first%20value%20to%20average%20is%20in%20A8%2C%20for%20example%2C%20and%20from%20A1%20to%20A7%2C%20there%20are%20other%20data%2C%20such%20as%20text%20dates%20or%20empty%20cells%2C%20it%20does%20not%20work%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3473633%22%20slang%3D%22es-ES%22%3ERe%3A%20Calculate%20average%20of%20a%20dynamic%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3473633%22%20slang%3D%22es-ES%22%3EI%20have%20already%20managed%20to%20make%20it%20work%2C%20very%20grateful%20for%20the%20suggestions.%3C%2FLINGO-BODY%3E
Contributor

Saludos a todos. Agradecería mucho su ayuda en la solución de la siguiente situación: tengo un rango de datos; digamos A1:A100 y en la celda B1 una cantidad aleatoria, digamos 10; lo que me gustaría es calcular el promedio en el rango A1:A100, dependiendo de la cantidad especificada en B1, en este caso 10;es decir que necesito calcular, según los datos anteriores el promedio de A1:A10, pero si en B1 pongo 20; entonces el calculo seria para A1:A20. Lo mas cerca que he estado de la solución es esto: =SI(FILAS(A1:A10)>=B1;PROMEDIO(DESREF(A10;;;-B1));""), pero funciona limitadamente.

Agradezco cualquier comentario. Gracias

5 Replies

@a7024782 

=AVERAGE(INDIRECT("A1:A"&B1))
=PROMEDIO(INDIRECTO("A1:A"&B1))

You can try this formula.

average.JPG 

@a7024782 

As variant

=AVERAGE(A1:INDEX(A:A,B1))
=PROMEDIO(A1:INDICE(A:A;B1))
Gracias por su ayuda en mi caso no ha funcionado,
Creo que no ha funcionado por lo siguiente: en el ejemplo enviado para ser analizado mencione el rango A1:A100; estando en A1 el primer valor a promediar y así hasta la celda A100. En ese caso si funciona; pero en el caso de que el primer valor a promediar este en A8, por ejemplo, y desde A1 hasta A7, estén otros datos, como texto fechas o celdas vacías,no funciona
Ya he logrado que funcione, muy agradecido por las sugerencias.