Hola a todos.

Tengo una tabla de excel en el que hay dos columnas con números que siempre son los mismos, y otra en el que hay datos que van variando. A continuación hay dos columnas más en las que se operan las dos anteriores en función de una celda, D24 en amarillo, de tal manera que la suma de todos los números positivos de la columna G, mostrada en la celda G24 de color verde, sea lo más próxima a 32 sin superarlo.

Por más vueltas que le doy, no encuentro una manera automática de que me lo haga y siempre lo tengo que hacer a mano.

¿Alguna solución para que lo haga de forma automática?

No sé si es exactamente éste el lugar más apropiado para este tema, si no es así, mis disculpas.

Muchas gracias.

menor y más próximo a 32.xls

Hola! empleando formulas no se me ocurre nada al respecto. Solo se me ocurre emplear buscar objetivo o en su defecto emplear solver.

No mencionas si el numero en D24 debe ser entero o no?

Puede ser igual a 32 la suma de los numeros positivos en la columna G?

Para obtener la suma en G24 introduce la siguiente formula matricial:

=SUMA(SI(G5:G20>0,G5:G20,0))

Te recomiendo que busques en internet acerca del uso de buscar objetivo, solver y formulas matriciales.

Yo utilice buscar objetivo y me devolvio:

6.08544923256345 para D24 con lo cual obtengo 32 exacto en en G24

Subo el ejemplo con la formula matricial en G21 y los resultado que me arrojo buscar objetivo en D24!

Suerte!

menor y más próximo a 32.xls

Hola Soalde

Te complicaste mucho con la fórmula de G24. En principio puedes sustituirla por esta:

=SUMAR.SI(G5:G20;">"&0;G5:G20)

La solución sería algo así como la que sigue. Digo "algo así" porque no sé qué papel juega el nº 6 que tienes en D24, que dices hay que cambiar a mano, ¿dónde hay que cambiarlo? ¿qué quieres hacer con él?

=SI(SUMAR.SI(G5:G20;">"&0;G5:G20)<=32;SUMAR.SI(G5:G20;">"&0;G5:G20);"")

Esta fórmula te dará celda en blanco (o sea comillas "") cuando la suma sea mayor que 32. Por cierto, no queda del todo claro si puede ser igual a 32 ó bien tiene que ser forzosamente menor. En este último caso suprimiríamos el signo igual (=)

Pregunto: Cuando la suma sea mayor que 32 ¿qué resultado quieres obtener?

Mira si puedes aclararlo

Un saludo

y otro para Digitalboy que acabo de ver su mensaje después de enviar este.

Yo usaria Sumar.si

=SUMAR.SI($G$5:$G$20,">"&0)

Hola de nuevo.

Efectivamente, el valor D24 tiene que ser un número entero, sin decimales.

Yo lo que hago es ir cambiando la celda a mano, probando números hasta que veo que en G24 me sale un número lo más próximo a 32 pero por debajo.

De ahí que quería saber si hay alguna solución para que me lo haga automáticamente.

Por cierto, gracias por la abreviatura de sumar sólo números positivos.

Hola de nuevo.

Efectivamente, el valor D24 tiene que ser un número entero, sin decimales.

Yo lo que hago es ir cambiando la celda a mano, probando números hasta que veo que en G24 me sale un número lo más próximo a 32 pero por debajo.

De ahí que quería saber si hay alguna solución para que me lo haga automáticamente.

Insisto... ya probaste buscar objetivo?

Hola,

también podrías usar una fórmula como (no la he simplificado mucho, sólo despejé y ya):

=([B][COLOR=#0000ff]32[/COLOR][/B]+SUMAPRODUCTO((G7:G22>0)*(D7:D22-C7:C22)))/CONTAR.SI(G7:G22;">0")[/CODE]

Ten en cuenta que al despejar la fórmula usas los valores que ya tienes, por lo que no puedes incorporar el resultado de la fórmula directamente en tu cálculo o te dará error de referenca circular.

Revisa el adjunto.

Claro está, si quieres sólo el entero puedes usar la función entero para envolver la fórmula o quitar los decimales en el formato de la celda o la función truncar o redondear o no sé, lo que mejor se ajuste.

menor y más próximo a 32.xls

Muchas gracias, Mauricio, pero esa fórmula no funciona todo lo bien que debería. Si pongo valores en las celdas D7:D22 que me tienen que dar un valor de 10, me devuelve un valor de 14.

Supongo que habrá que utilizar la función solver, pero por más información que consulto, me supera.

En fin, seguiré haciéndolo a mano, que tampoco pasa nada. Muchísimas gracias a todos los que habéis intentado ayudarme.

Hola,

tienes razón, cómo lo mencioné al inicio, la fórmula se basa en los valores ya existentes, por eso tiene un "sesgo" y no la puedes usar directamente en el cálculo pues te dará error de referencia circular. Para solucionarlo bastaría con activar el cálculo iterativo y usar la fórmula directamente en el cálculo, es decir, usándola en D25. Así la fórmula ya no te dará un resultado sesgado por los valores actuales.

Sin embargo, yo seguiría la recomendación de nuestro compañero y experto digitalboy: usa buscar objetivo. Es mucho más sencillo que solver y para tu caso es suficiente, te dará el resultado, sólo tendrás que extraer el entero tu mismo.

Va un adjunto con una minimacro que te ejecuta el buscar objetivo automáticamente .

(o usa la fórmula que te dí en D25 y activa el cálculo iterativo).

menor y más próximo a 32.xls

Muchas gracias, pero mi intención es que lo calculara automáticamente, no haciendo buscar objetivo.

He visto la macro y funciona a las mil maravillas. No tengo ni idea de cómo se hace, pero ya buscaré info para copiarla y pegarla en mi libro de cálculo.

Un saludo y muchas gracias de nuevo.

-EDIT-

Ahora que lo pienso, ¿no me podrías pasar un excel con el cálculo iterativo hecho? No quisiera parecer un caras, ni mucho menos, pero reconozco que mi nivel en excel no es lo bastante bueno; al menos sé dónde se activa lo de cálculo iterativo en el excel 2003 . Luego yo ya trataría de ver cómo va la fórmula y tratar de adaptarla a mi libro.

Mil disculpas por las molestias.