Excel - Maximos y Minimos?

Asked By HMS on 26-Oct-09 12:45 PM
Hola a tod@s.
Tengo una base de datos con dos columnas.
En la primer columna se encuentras datos de un puntaje obtenido,
Y en la segunda columna tengo otra lista de valores con Montos
Ejem
Puntaje     Cant
140          100.00
140	       110.00
140	       115.00
188	       120.00
190	       125.00
191	       130.00
250	       135.00
249	       140.00
250	       145.00
260	       150.00
266	       155.00
269	       160.00

Adicionalmente, tengo un cuadro en el que que aparecen unos intervalos
con l=EDmites, de los puntajes obtenidos.
Lim Inf      Lim Superior
140	          250
251	          350

Bien, ahora lo que necesito, es que con base a la tabla de Intervalos
de l=EDmites, encontrar una f=F3rmula, que me indique para cada intervalo,
cual es el valor M=EDnimo y M=E1ximo del Monto.

Por ejemplo.
Para el limite (140-250), el valor M=EDnimo es:100, y el m=E1ximo es:135
Para el limite (251-350), el valor M=EDnimo es:140, y el m=E1ximo es:160.

De antemano, muchas gracias.
HMS




Juan M replied on 16-Oct-09 11:57 AM
Hola,

A la espera de otras propuestas:
Suponiendo tu rango de datos en A2:B13
En D2:E2 los extremos del primer intervalo
En D3:E3 los extremos del segundo intervalo
...

Para el maximo:
(formula matricial) se confirma con ctrl+Alt+enter
=MAX((A2:A13>=D2)*(A2:A13<=E2)*B2:B13)

Para el mínimo:
(formula matricial) se confirma con ctrl+Alt+enter
=K.ESIMO.MENOR((A2:A13>=D2)*(A2:A13<=E2)*(B2:B13);CONTAR(A2:A13)-SUMAPRODUCTO((A2:A13>=D2)*(A2:A13<=E2))+1)

Un saludo,
Juan


---------Consulta Original

Hola a tod@s.
Tengo una base de datos con dos columnas.
En la primer columna se encuentras datos de un puntaje obtenido,
Y en la segunda columna tengo otra lista de valores con Montos
Ejem
Puntaje     Cant
140          100.00
140        110.00
140        115.00
188        120.00
190        125.00
191        130.00
250        135.00
249        140.00
250        145.00
260        150.00
266        155.00
269        160.00

Adicionalmente, tengo un cuadro en el que que aparecen unos intervalos
con límites, de los puntajes obtenidos.
Lim Inf      Lim Superior
140           250
251           350

Bien, ahora lo que necesito, es que con base a la tabla de Intervalos
de límites, encontrar una fórmula, que me indique para cada intervalo,
cual es el valor Mínimo y Máximo del Monto.

Por ejemplo.
Para el limite (140-250), el valor Mínimo es:100, y el máximo es:135
Para el limite (251-350), el valor Mínimo es:140, y el máximo es:160.

De antemano, muchas gracias.
HMS
Gabriel Raigosa replied on 16-Oct-09 12:03 PM
Hola;

Te sirve la soluci?n con tablas din?micas?

G. Raigosa

HMS escribi?:
Manolo replied on 16-Oct-09 05:26 PM
Hola Juan, una alternativa para encontrar el valor mínimo puede ser la
siguiente fórmula (matricial):

=MIN(SI($A$2:$A$13>=D2,1,5000)*SI($A$2:$A$13<=E2,1,5000)*($B$2:$B$13))

Saludos

Manolo
Juan M replied on 17-Oct-09 04:13 AM
Hola de nuevo,

fe de erratas


Las f?rmulas matriciales se confirman con ctrl+mayusc+enter
en que estar?a pensando.

Un saludo,
Juan
Juan M replied on 17-Oct-09 04:15 AM
Hola Manolo,

Interesante propuesta, como mejora se podr?a poner en lugar del 5000, ya que
no sabemos el rango de valores que maneja OP, el valor del m?ximo de toda la
lista.


por

=MIN(SI($A$2:$A$13>=D2;1;MAX($B$2:$B$13))*SI($A$2:$A$13<=E2;1;MAX($B$2:$B$13))*($B$2:$B$13))

las dos matriciales (se confirman con ctrl+mayusc+enter)

Un saludo,
Juan

PD: disculpa Manolo si te llega un mensaje al correo personal, la intencion
era enviarlo aqui.
Héctor Miguel replied on 17-Oct-09 05:14 AM
hola, chicos !

si ya se utilizan solo formulas matriciales... probar con los siguientes "recortes"...

=min(si($a$2:$a$13>=d2,si($a$2:$a$13<=e2,$b$2:$b$13)))
=max(si($a$2:$a$13>=d2,si($a$2:$a$13<=e2,$b$2:$b$13)))

saludos,
hector.
Gabriel Raigosa replied on 17-Oct-09 02:52 PM
Hola;

Interesantes soluciones

Les aporto una soluci?n con tablas din?micas,en el vinculo;

http://sites.google.com/site/misitioexcel/Excel/archivos-para-el-foro

Encuentran un archivo llamado


(Determinar el m?ximo y el m?nimo valor, dentro de rangos establecidos)

G. Raigosa



HMS escribi?:
Gabriel Raigosa replied on 17-Oct-09 07:17 PM
Hola Hector;

Si contemple la posibilidad de rangos arbitrarios, en cuyo caso las TD
no serian ?tiles, y desde la consulta inicial se observa.

=> 140..250, intervalo: 110
=> 251..350, Intervalo: 99

La propuesta aplica para de rangos de "igual tama?o".

La columna auxiliar no la use para agrupar, solo la use para mostar a
que rango pertenece cada calificaci?n.

La TD solo usa la columna "A" y "B".

Me parece bien interesante la consulta.

G. Raigosa

H?ctor Miguel escribi?:
Gabriel Raigosa replied on 17-Oct-09 07:36 PM
Hector,

Una pregunta,

Si los rangos son "id?nticos",

?cual seria el problema que fueran mas de 35, 3500 o 35000?

Aun no alcanzo a ver que inconveniente se presentar?a.

G. Raigosa


H?ctor Miguel escribi?:
Héctor Miguel replied on 17-Oct-09 09:42 PM
hola, Gabriel !


solo cuando quisieras mostrar/comprobar/... a que "rango" pertenece cada registro...
tendrias 35, 3500 o 35000 buscarv(... recalculandose al modificar la tabla donde se busca  ;)

saludos,
hector.
Gabriel Raigosa replied on 17-Oct-09 10:17 PM
Hola Hector,

?Entonces es la comprobaci?n, la que pudiera hacer lento el proceso de
calculo?

Si no se hacen comprobaciones, y se conf?a en que las TD's hacen su
trabajo (para los rangos que las TD agrupan) pienso que es confiable el
resultado.

Pienso que no habria necesidad de hacer comprobaciones con los n's (35,
3500, 35000, buscarv).

G. Raigosa


H?ctor Miguel escribi?:
Héctor Miguel replied on 17-Oct-09 10:27 PM
hola, Gabriel !


por supuesto, cualquier formula no necesaria consume recursos innecesariamente


la confianza en que las herramientas "hacen su trabajo"... viene despues de su comprobacion  :))


en todo caso, quedaria la salvedad de la "constante" en los saltos entre los rangos (?)

saludos,
hector.
Gabriel Raigosa replied on 17-Oct-09 10:38 PM
Hector,

Gracias por tus respuestas.

Para mi es claro que las TD agrupan con "saltos" constantes. He seguido
el tema y me llamo mucho la atenci?n. Las TD no agrupan a gusto del usuario.

Estas preguntas "aparentemente simples" son bastante llamativas.

G. Raigosa



H?ctor Miguel escribi?:
Juan M replied on 19-Oct-09 05:52 PM
Hola Hector,

Las dos formulas son muy ... iguales, fenomenal.
Solo una duda me ha surgido ya que, si bien con la formula que propuse
originalmente para los m?nimos he sobrecargado de operaciones y funciones,
la formula propuesta para los maximos es mas corta que la que has propuesto,
incluso emplea menos funciones (solo comparaciones).
?hay algo que se me haya escapado en esa?

Un saludo,
Juan
HMS replied on 26-Oct-09 12:45 PM
s
es,
to,

Bueno amigos!!!
Excelentes los aportes.
Ciertamente las TD, permiten un an=E1lisis amplio de cualquier BD, en
realidad pienso que esta herramienta aplica tambi=E9n para resolver el
problema.
Sin embargo, en esta situaci=F3n en particular, lo que se requiere, es
una f=F3rmula "est=E1tica", que permita tener el dato en forma directa,
sin tener que indicarle al usuario, como actualizar la TD, etc.

De nuevo gracias !
HNS
Gabriel Raigosa replied on 22-Oct-09 12:55 PM
Hola,

No esta por dem?s comentar que se puede hacer una macro sencilla que
actualice los datos de la tabla din?mica al seleccionar la hoja, si mal
no recuerdo es un "evento", que al seleccionar la hoja que tiene la TD
lanza alguna macro y que actualice la TD sin intervenci?n del usuario.

Gabriel

HMS escribi?: