Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Questions about functions
I have a column of numbers, and what I want to do is return the 7 highest of
the numbers. Then, I want to be able to return the row number of each one them. The only problem I have is, if two or more of the high numbers are the same, it will keep returning the same the row number, instead of the next and so on. And I CANT use the sort functions because I dont wanna mess up my values Any suggestions? Please let me know. lol It's probably an easy answer, I just cant find it. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Questions about functions
Try this:
Numbers in the range A1:A20 Enter this formula in D1: =LARGE(A$1:A$20,ROWS($1:1)) Enter this array formula** in E1: =SMALL(IF(A$1:A$20=D1,ROW(A$1:A$20)),COUNTIF(D$1:D 1,D1)) Select both D1 and E1 and copy down a total of 7 rows or as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Cory from Eugene" wrote in message ... I have a column of numbers, and what I want to do is return the 7 highest of the numbers. Then, I want to be able to return the row number of each one them. The only problem I have is, if two or more of the high numbers are the same, it will keep returning the same the row number, instead of the next and so on. And I CANT use the sort functions because I dont wanna mess up my values Any suggestions? Please let me know. lol It's probably an easy answer, I just cant find it. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Questions about functions
T. Walko,
Thanks for the help. I got one more question? Can you by chance translate into Quatro Pro form? I cant find a quattro pro help site anywhere! Please let me know, thanks, Cory "T. Valko" wrote: Try this: Numbers in the range A1:A20 Enter this formula in D1: =LARGE(A$1:A$20,ROWS($1:1)) Enter this array formula** in E1: =SMALL(IF(A$1:A$20=D1,ROW(A$1:A$20)),COUNTIF(D$1:D 1,D1)) Select both D1 and E1 and copy down a total of 7 rows or as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Cory from Eugene" wrote in message ... I have a column of numbers, and what I want to do is return the 7 highest of the numbers. Then, I want to be able to return the row number of each one them. The only problem I have is, if two or more of the high numbers are the same, it will keep returning the same the row number, instead of the next and so on. And I CANT use the sort functions because I dont wanna mess up my values Any suggestions? Please let me know. lol It's probably an easy answer, I just cant find it. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Questions about functions
Sorry, haven't used QP since the mid 90's!
-- Biff Microsoft Excel MVP "Cory from Eugene" wrote in message ... T. Walko, Thanks for the help. I got one more question? Can you by chance translate into Quatro Pro form? I cant find a quattro pro help site anywhere! Please let me know, thanks, Cory "T. Valko" wrote: Try this: Numbers in the range A1:A20 Enter this formula in D1: =LARGE(A$1:A$20,ROWS($1:1)) Enter this array formula** in E1: =SMALL(IF(A$1:A$20=D1,ROW(A$1:A$20)),COUNTIF(D$1:D 1,D1)) Select both D1 and E1 and copy down a total of 7 rows or as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Cory from Eugene" wrote in message ... I have a column of numbers, and what I want to do is return the 7 highest of the numbers. Then, I want to be able to return the row number of each one them. The only problem I have is, if two or more of the high numbers are the same, it will keep returning the same the row number, instead of the next and so on. And I CANT use the sort functions because I dont wanna mess up my values Any suggestions? Please let me know. lol It's probably an easy answer, I just cant find it. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Questions about functions
T. Valko,
I tried the example you mention in Excel, and it didnt work. For the D1-D7 cells, all it returned was "TRUE". Then, in the E1-E7 cells it returned "#NUM!". Any ideas? Thanks, Cory "T. Valko" wrote: Sorry, haven't used QP since the mid 90's! -- Biff Microsoft Excel MVP "Cory from Eugene" wrote in message ... T. Walko, Thanks for the help. I got one more question? Can you by chance translate into Quatro Pro form? I cant find a quattro pro help site anywhere! Please let me know, thanks, Cory "T. Valko" wrote: Try this: Numbers in the range A1:A20 Enter this formula in D1: =LARGE(A$1:A$20,ROWS($1:1)) Enter this array formula** in E1: =SMALL(IF(A$1:A$20=D1,ROW(A$1:A$20)),COUNTIF(D$1:D 1,D1)) Select both D1 and E1 and copy down a total of 7 rows or as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Cory from Eugene" wrote in message ... I have a column of numbers, and what I want to do is return the 7 highest of the numbers. Then, I want to be able to return the row number of each one them. The only problem I have is, if two or more of the high numbers are the same, it will keep returning the same the row number, instead of the next and so on. And I CANT use the sort functions because I dont wanna mess up my values Any suggestions? Please let me know. lol It's probably an easy answer, I just cant find it. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Questions about functions
Here's a small sample file that demonstrates this:
delete.xls 14kb http://cjoint.com/?iFhvKXIgVr -- Biff Microsoft Excel MVP "Cory from Eugene" wrote in message ... T. Valko, I tried the example you mention in Excel, and it didnt work. For the D1-D7 cells, all it returned was "TRUE". Then, in the E1-E7 cells it returned "#NUM!". Any ideas? Thanks, Cory "T. Valko" wrote: Sorry, haven't used QP since the mid 90's! -- Biff Microsoft Excel MVP "Cory from Eugene" wrote in message ... T. Walko, Thanks for the help. I got one more question? Can you by chance translate into Quatro Pro form? I cant find a quattro pro help site anywhere! Please let me know, thanks, Cory "T. Valko" wrote: Try this: Numbers in the range A1:A20 Enter this formula in D1: =LARGE(A$1:A$20,ROWS($1:1)) Enter this array formula** in E1: =SMALL(IF(A$1:A$20=D1,ROW(A$1:A$20)),COUNTIF(D$1:D 1,D1)) Select both D1 and E1 and copy down a total of 7 rows or as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Cory from Eugene" wrote in message ... I have a column of numbers, and what I want to do is return the 7 highest of the numbers. Then, I want to be able to return the row number of each one them. The only problem I have is, if two or more of the high numbers are the same, it will keep returning the same the row number, instead of the next and so on. And I CANT use the sort functions because I dont wanna mess up my values Any suggestions? Please let me know. lol It's probably an easy answer, I just cant find it. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Questions about functions
Did you try typing in the formulae? If so, you probably made a typing
error. Just copy the formulae from Biff's message & paste into the formula bar for the relevant cells. And read his instructions carefully. -- David Biddulph "Cory from Eugene" wrote in message ... T. Valko, I tried the example you mention in Excel, and it didnt work. For the D1-D7 cells, all it returned was "TRUE". Then, in the E1-E7 cells it returned "#NUM!". Any ideas? Thanks, Cory .... "T. Valko" wrote: Try this: Numbers in the range A1:A20 Enter this formula in D1: =LARGE(A$1:A$20,ROWS($1:1)) Enter this array formula** in E1: =SMALL(IF(A$1:A$20=D1,ROW(A$1:A$20)),COUNTIF(D$1:D 1,D1)) Select both D1 and E1 and copy down a total of 7 rows or as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Cory from Eugene" wrote in message ... I have a column of numbers, and what I want to do is return the 7 highest of the numbers. Then, I want to be able to return the row number of each one them. The only problem I have is, if two or more of the high numbers are the same, it will keep returning the same the row number, instead of the next and so on. And I CANT use the sort functions because I dont wanna mess up my values Any suggestions? Please let me know. lol It's probably an easy answer, I just cant find |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Questions about functions
T. Valko,
Hey, i just wanted to say that your first solution was the right one and it worked!! Thanks so much. I just have one more question. Do you know how to do the same thing for searching columns? I tried to do it using the same ideas as the row functions, but it didnt work. Thanks so much!! Cory "T. Valko" wrote: Try this: Numbers in the range A1:A20 Enter this formula in D1: =LARGE(A$1:A$20,ROWS($1:1)) Enter this array formula** in E1: =SMALL(IF(A$1:A$20=D1,ROW(A$1:A$20)),COUNTIF(D$1:D 1,D1)) Select both D1 and E1 and copy down a total of 7 rows or as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Cory from Eugene" wrote in message ... I have a column of numbers, and what I want to do is return the 7 highest of the numbers. Then, I want to be able to return the row number of each one them. The only problem I have is, if two or more of the high numbers are the same, it will keep returning the same the row number, instead of the next and so on. And I CANT use the sort functions because I dont wanna mess up my values Any suggestions? Please let me know. lol It's probably an easy answer, I just cant find it. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Questions about functions
David,
yeah, his help did the trick! Now i just need to do the same for searching columns, but cant figure it out. Any ideas? Thanks, Cory "David Biddulph" wrote: Did you try typing in the formulae? If so, you probably made a typing error. Just copy the formulae from Biff's message & paste into the formula bar for the relevant cells. And read his instructions carefully. -- David Biddulph "Cory from Eugene" wrote in message ... T. Valko, I tried the example you mention in Excel, and it didnt work. For the D1-D7 cells, all it returned was "TRUE". Then, in the E1-E7 cells it returned "#NUM!". Any ideas? Thanks, Cory .... "T. Valko" wrote: Try this: Numbers in the range A1:A20 Enter this formula in D1: =LARGE(A$1:A$20,ROWS($1:1)) Enter this array formula** in E1: =SMALL(IF(A$1:A$20=D1,ROW(A$1:A$20)),COUNTIF(D$1:D 1,D1)) Select both D1 and E1 and copy down a total of 7 rows or as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Cory from Eugene" wrote in message ... I have a column of numbers, and what I want to do is return the 7 highest of the numbers. Then, I want to be able to return the row number of each one them. The only problem I have is, if two or more of the high numbers are the same, it will keep returning the same the row number, instead of the next and so on. And I CANT use the sort functions because I dont wanna mess up my values Any suggestions? Please let me know. lol It's probably an easy answer, I just cant find |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Questions about functions
The formulas I posted (and the sample file) are based on the data being in
columns. Like this: 10 22 51 39 If you want to do the same thing for data running *across* a row, like this: 10...22...51...39 Then the formulas would be something like this: Data in the range A1:Z1 Entered in A3: =LARGE($A1:$Z1,COLUMNS($A:A)) Array formula** entered in A4: =SMALL(IF($A1:$Z1=A3,COLUMN($A1:$Z1)),COUNTIF($A3: A3,A3)) Select both A3 and A4 and copy across as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Cory from Eugene" wrote in message ... David, yeah, his help did the trick! Now i just need to do the same for searching columns, but cant figure it out. Any ideas? Thanks, Cory "David Biddulph" wrote: Did you try typing in the formulae? If so, you probably made a typing error. Just copy the formulae from Biff's message & paste into the formula bar for the relevant cells. And read his instructions carefully. -- David Biddulph "Cory from Eugene" wrote in message ... T. Valko, I tried the example you mention in Excel, and it didnt work. For the D1-D7 cells, all it returned was "TRUE". Then, in the E1-E7 cells it returned "#NUM!". Any ideas? Thanks, Cory .... "T. Valko" wrote: Try this: Numbers in the range A1:A20 Enter this formula in D1: =LARGE(A$1:A$20,ROWS($1:1)) Enter this array formula** in E1: =SMALL(IF(A$1:A$20=D1,ROW(A$1:A$20)),COUNTIF(D$1:D 1,D1)) Select both D1 and E1 and copy down a total of 7 rows or as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Cory from Eugene" wrote in message ... I have a column of numbers, and what I want to do is return the 7 highest of the numbers. Then, I want to be able to return the row number of each one them. The only problem I have is, if two or more of the high numbers are the same, it will keep returning the same the row number, instead of the next and so on. And I CANT use the sort functions because I dont wanna mess up my values Any suggestions? Please let me know. lol It's probably an easy answer, I just cant find |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Questions about functions
Biff, you're the man. thanks for the help
Cory "T. Valko" wrote: The formulas I posted (and the sample file) are based on the data being in columns. Like this: 10 22 51 39 If you want to do the same thing for data running *across* a row, like this: 10...22...51...39 Then the formulas would be something like this: Data in the range A1:Z1 Entered in A3: =LARGE($A1:$Z1,COLUMNS($A:A)) Array formula** entered in A4: =SMALL(IF($A1:$Z1=A3,COLUMN($A1:$Z1)),COUNTIF($A3: A3,A3)) Select both A3 and A4 and copy across as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Cory from Eugene" wrote in message ... David, yeah, his help did the trick! Now i just need to do the same for searching columns, but cant figure it out. Any ideas? Thanks, Cory "David Biddulph" wrote: Did you try typing in the formulae? If so, you probably made a typing error. Just copy the formulae from Biff's message & paste into the formula bar for the relevant cells. And read his instructions carefully. -- David Biddulph "Cory from Eugene" wrote in message ... T. Valko, I tried the example you mention in Excel, and it didnt work. For the D1-D7 cells, all it returned was "TRUE". Then, in the E1-E7 cells it returned "#NUM!". Any ideas? Thanks, Cory .... "T. Valko" wrote: Try this: Numbers in the range A1:A20 Enter this formula in D1: =LARGE(A$1:A$20,ROWS($1:1)) Enter this array formula** in E1: =SMALL(IF(A$1:A$20=D1,ROW(A$1:A$20)),COUNTIF(D$1:D 1,D1)) Select both D1 and E1 and copy down a total of 7 rows or as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Cory from Eugene" wrote in message ... I have a column of numbers, and what I want to do is return the 7 highest of the numbers. Then, I want to be able to return the row number of each one them. The only problem I have is, if two or more of the high numbers are the same, it will keep returning the same the row number, instead of the next and so on. And I CANT use the sort functions because I dont wanna mess up my values Any suggestions? Please let me know. lol It's probably an easy answer, I just cant find |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Questions about functions
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Cory from Eugene" wrote in message ... Biff, you're the man. thanks for the help Cory "T. Valko" wrote: The formulas I posted (and the sample file) are based on the data being in columns. Like this: 10 22 51 39 If you want to do the same thing for data running *across* a row, like this: 10...22...51...39 Then the formulas would be something like this: Data in the range A1:Z1 Entered in A3: =LARGE($A1:$Z1,COLUMNS($A:A)) Array formula** entered in A4: =SMALL(IF($A1:$Z1=A3,COLUMN($A1:$Z1)),COUNTIF($A3: A3,A3)) Select both A3 and A4 and copy across as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Cory from Eugene" wrote in message ... David, yeah, his help did the trick! Now i just need to do the same for searching columns, but cant figure it out. Any ideas? Thanks, Cory "David Biddulph" wrote: Did you try typing in the formulae? If so, you probably made a typing error. Just copy the formulae from Biff's message & paste into the formula bar for the relevant cells. And read his instructions carefully. -- David Biddulph "Cory from Eugene" wrote in message ... T. Valko, I tried the example you mention in Excel, and it didnt work. For the D1-D7 cells, all it returned was "TRUE". Then, in the E1-E7 cells it returned "#NUM!". Any ideas? Thanks, Cory .... "T. Valko" wrote: Try this: Numbers in the range A1:A20 Enter this formula in D1: =LARGE(A$1:A$20,ROWS($1:1)) Enter this array formula** in E1: =SMALL(IF(A$1:A$20=D1,ROW(A$1:A$20)),COUNTIF(D$1:D 1,D1)) Select both D1 and E1 and copy down a total of 7 rows or as needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Cory from Eugene" wrote in message ... I have a column of numbers, and what I want to do is return the 7 highest of the numbers. Then, I want to be able to return the row number of each one them. The only problem I have is, if two or more of the high numbers are the same, it will keep returning the same the row number, instead of the next and so on. And I CANT use the sort functions because I dont wanna mess up my values Any suggestions? Please let me know. lol It's probably an easy answer, I just cant find |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula/functions for average and if functions | Excel Worksheet Functions | |||
efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
Several function questions (nested functions) | Excel Worksheet Functions | |||
3 questions about automated c++ com add-in worksheet functions | Excel Worksheet Functions | |||
Array Functions - Two Questions | Excel Worksheet Functions |