Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SMALL(?)
Hi, I have a question about this function:
Well it's not exactly that function it's the opposite one (BIG?) I don't know its name, because in my Excel it's called K.ESIMO.MAYOR. I have a table with numbers and names and I want the name of the biggest number, the second biggest, etc... How do I get this through formulas? numbers may be repeated, but just one would be enough. With K.ESIMO.MAYOR I can get the biggest number, but now how do I use this in another formula to get the name? I'm not good with references so can you help me out? Regards, -- Beto Reply: Erase between the dot (inclusive) and the @. Responder: Borra la frase obvia y el punto previo. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SMALL(?)
Beto, you can use INDEX (INDICE) after you get the small (K.ESIMO.MENOR) or
large (K.ESIMO.MAYOR) number, something like =INDEX(A1:A100, MATCH(LARGE(B1:B100,3), B1:B100, 0)) or in spanish: =INDICE(A1:A100, COINCIDIR(K.ESIMO.MAYOR(B1:B100,3), B1:B100, 0)) where A1:A100 house the names, and B1:B100 the numbers. -- Regards, Juan Pablo González "Beto" wrote in message ... Hi, I have a question about this function: Well it's not exactly that function it's the opposite one (BIG?) I don't know its name, because in my Excel it's called K.ESIMO.MAYOR. I have a table with numbers and names and I want the name of the biggest number, the second biggest, etc... How do I get this through formulas? numbers may be repeated, but just one would be enough. With K.ESIMO.MAYOR I can get the biggest number, but now how do I use this in another formula to get the name? I'm not good with references so can you help me out? Regards, -- Beto Reply: Erase between the dot (inclusive) and the @. Responder: Borra la frase obvia y el punto previo. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SMALL(?)
Juan Pablo González wrote:
Beto, you can use INDEX (INDICE) after you get the small (K.ESIMO.MENOR) or large (K.ESIMO.MAYOR) number, something like =INDEX(A1:A100, MATCH(LARGE(B1:B100,3), B1:B100, 0)) or in spanish: =INDICE(A1:A100, COINCIDIR(K.ESIMO.MAYOR(B1:B100,3), B1:B100, 0)) where A1:A100 house the names, and B1:B100 the numbers. Hi, It's working as it is. It's almost what I need... now the problem arises when there are two equal numbers, because it will display the same name for "different" numbers. Let's say I want the Top 5 of the list, I get the top five numbers (one is repeated) but the associated name is wrong in one of them... any way to correct this? Regards, -- Beto Reply: Erase between the dot (inclusive) and the @. Responder: Borra la frase obvia y el punto previo. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SMALL(?)
Beto, can you see if this approach by Aladin Akyurek works for you ?
http://www.mrexcel.com/board2/viewtopic.php?t=74990 -- Regards, Juan Pablo González "Beto" wrote in message ... Juan Pablo González wrote: Beto, you can use INDEX (INDICE) after you get the small (K.ESIMO.MENOR) or large (K.ESIMO.MAYOR) number, something like =INDEX(A1:A100, MATCH(LARGE(B1:B100,3), B1:B100, 0)) or in spanish: =INDICE(A1:A100, COINCIDIR(K.ESIMO.MAYOR(B1:B100,3), B1:B100, 0)) where A1:A100 house the names, and B1:B100 the numbers. Hi, It's working as it is. It's almost what I need... now the problem arises when there are two equal numbers, because it will display the same name for "different" numbers. Let's say I want the Top 5 of the list, I get the top five numbers (one is repeated) but the associated name is wrong in one of them... any way to correct this? Regards, -- Beto Reply: Erase between the dot (inclusive) and the @. Responder: Borra la frase obvia y el punto previo. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SMALL(?)
Juan Pablo González wrote:
Beto, can you see if this approach by Aladin Akyurek works for you ? http://www.mrexcel.com/board2/viewtopic.php?t=74990 Hi, It might work... but I'll need to give it a more thorough look. Thanks for the link. Regards, -- Beto Reply: Erase between the dot (inclusive) and the @. Responder: Borra la frase obvia y el punto previo. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
SMALL(?)
You can use the LARGE function in combination with Vlookup
A1:A10 are numbers B1:B10 are names =VLOOKUP(LARGE(A1:A10,1),A1:B10,2) Change the 1 to two if you want the second biggest -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Beto" wrote in message ... Hi, I have a question about this function: Well it's not exactly that function it's the opposite one (BIG?) I don't know its name, because in my Excel it's called K.ESIMO.MAYOR. I have a table with numbers and names and I want the name of the biggest number, the second biggest, etc... How do I get this through formulas? numbers may be repeated, but just one would be enough. With K.ESIMO.MAYOR I can get the biggest number, but now how do I use this in another formula to get the name? I'm not good with references so can you help me out? Regards, -- Beto Reply: Erase between the dot (inclusive) and the @. Responder: Borra la frase obvia y el punto previo. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
SMALL(?)
Ron de Bruin wrote:
You can use the LARGE function in combination with Vlookup A1:A10 are numbers B1:B10 are names =VLOOKUP(LARGE(A1:A10,1),A1:B10,2) Change the 1 to two if you want the second biggest Thanks, this answer my question, but my question was badly answered. What I need isn't fulfilled by this. I get the same "bad names" as I get with the other response I got. Besides I'd need to reorder the columns, because I have A:A = Names B:B = Numbers, not as VLOOKUP expects it. Thanks anyway Ron. Regards, -- Beto Reply: Erase between the dot (inclusive) and the @. Responder: Borra la frase obvia y el punto previo. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
SMALL(?)
Look here
http://www.cpearson.com/excel/lookups.htm#LeftLookup -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Beto" wrote in message ... Ron de Bruin wrote: You can use the LARGE function in combination with Vlookup A1:A10 are numbers B1:B10 are names =VLOOKUP(LARGE(A1:A10,1),A1:B10,2) Change the 1 to two if you want the second biggest Thanks, this answer my question, but my question was badly answered. What I need isn't fulfilled by this. I get the same "bad names" as I get with the other response I got. Besides I'd need to reorder the columns, because I have A:A = Names B:B = Numbers, not as VLOOKUP expects it. Thanks anyway Ron. Regards, -- Beto Reply: Erase between the dot (inclusive) and the @. Responder: Borra la frase obvia y el punto previo. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
SMALL(?)
Ron de Bruin wrote:
Look here http://www.cpearson.com/excel/lookups.htm#LeftLookup Nice approach... I'll give it a try. Regards, -- Beto Reply: Erase between the dot (inclusive) and the @. Responder: Borra la frase obvia y el punto previo. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
SMALL(?)
=VLOOKUP(LARGE(A1:A10,1),A1:B10,2,FALSE)
I forgot to add the false -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Ron de Bruin" wrote in message ... You can use the LARGE function in combination with Vlookup A1:A10 are numbers B1:B10 are names =VLOOKUP(LARGE(A1:A10,1),A1:B10,2) Change the 1 to two if you want the second biggest -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Beto" wrote in message ... Hi, I have a question about this function: Well it's not exactly that function it's the opposite one (BIG?) I don't know its name, because in my Excel it's called K.ESIMO.MAYOR. I have a table with numbers and names and I want the name of the biggest number, the second biggest, etc... How do I get this through formulas? numbers may be repeated, but just one would be enough. With K.ESIMO.MAYOR I can get the biggest number, but now how do I use this in another formula to get the name? I'm not good with references so can you help me out? Regards, -- Beto Reply: Erase between the dot (inclusive) and the @. Responder: Borra la frase obvia y el punto previo. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SMALL IF | Excel Discussion (Misc queries) | |||
Help on {=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))} | New Users to Excel | |||
ISERROR,SMALL,INDEX, MATCH, SMALL?? | Excel Discussion (Misc queries) | |||
Using Small | Excel Worksheet Functions | |||
Small | Excel Discussion (Misc queries) |