| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Pass an array to Rank
Hi Folks!
Anyone know how to pass an array of values as the ref argument of the Rank function without hardcoding or using a range reference? Assume I have this array of values generated by another formula: {71;66;83;71;84} How do I pass that array to Rank? I can get the array passed but then the formula #VALUE! errors: =RANK(71,{71;66;83;71;84}) I've tried using a name for the array, Indexing, Indirect ??? I've never seen this done but I'm not certain that it can't be done. Biff |
|
#2
|
|||
|
|||
|
Hi Biff!
Unfortunately, I have no idea. Does the solution have to involve the RANK function or are you willing to use another alternative to get ranking? In article , "Biff" wrote: Hi Folks! Anyone know how to pass an array of values as the ref argument of the Rank function without hardcoding or using a range reference? Assume I have this array of values generated by another formula: {71;66;83;71;84} How do I pass that array to Rank? I can get the array passed but then the formula #VALUE! errors: =RANK(71,{71;66;83;71;84}) I've tried using a name for the array, Indexing, Indirect ??? I've never seen this done but I'm not certain that it can't be done. Biff |
|
#3
|
|||
|
|||
|
Hi Domenic!
I'm try to extract TEXT values sorted in ascending order. I can do it easily but it takes 2 helper columns. So what I'm trying to do is put it all together in a single formula and eliminate the need for the helpers. The list has dupes and that's what's proving to be a real bear. green black grass blue green I want to extract sorted based on the first letter. It doesn't matter if "black" or "blue" is listed first. So the extracted list would look like this: black blue green green grass Right now I use 2 helpers, 1 returns the code for the first letter: =CODE(UPPER(A1) The other helper is the Rank that break ties: =RANK(B1,B$1:B$5)+COUNTIF(B$1:B1,B1)-1 Then a simple INDEX/MATCH. Trying to put that all together in one formula! Good challenge for someone! Biff "Domenic" wrote in message ... Hi Biff! Unfortunately, I have no idea. Does the solution have to involve the RANK function or are you willing to use another alternative to get ranking? In article , "Biff" wrote: Hi Folks! Anyone know how to pass an array of values as the ref argument of the Rank function without hardcoding or using a range reference? Assume I have this array of values generated by another formula: {71;66;83;71;84} How do I pass that array to Rank? I can get the array passed but then the formula #VALUE! errors: =RANK(71,{71;66;83;71;84}) I've tried using a name for the array, Indexing, Indirect ??? I've never seen this done but I'm not certain that it can't be done. Biff |
|
#4
|
|||
|
|||
|
If you simply copy the list to another range and then perform Data/Sort,
Ascending, it will return black blue grass green green Is that satisfactory? Alan Beban Biff wrote: Hi Domenic! I'm try to extract TEXT values sorted in ascending order. I can do it easily but it takes 2 helper columns. So what I'm trying to do is put it all together in a single formula and eliminate the need for the helpers. The list has dupes and that's what's proving to be a real bear. green black grass blue green I want to extract sorted based on the first letter. It doesn't matter if "black" or "blue" is listed first. So the extracted list would look like this: black blue green green grass Right now I use 2 helpers, 1 returns the code for the first letter: =CODE(UPPER(A1) The other helper is the Rank that break ties: =RANK(B1,B$1:B$5)+COUNTIF(B$1:B1,B1)-1 Then a simple INDEX/MATCH. Trying to put that all together in one formula! Good challenge for someone! Biff "Domenic" wrote in message ... Hi Biff! Unfortunately, I have no idea. Does the solution have to involve the RANK function or are you willing to use another alternative to get ranking? In article , "Biff" wrote: Hi Folks! Anyone know how to pass an array of values as the ref argument of the Rank function without hardcoding or using a range reference? Assume I have this array of values generated by another formula: {71;66;83;71;84} How do I pass that array to Rank? I can get the array passed but then the formula #VALUE! errors: =RANK(71,{71;66;83;71;84}) I've tried using a name for the array, Indexing, Indirect ??? I've never seen this done but I'm not certain that it can't be done. Biff |
|
#5
|
|||
|
|||
|
That could be done but it would have to done every time the data changes. A
macro could also be used but the macro would have to run every time the data changes. I'm looking for a completely automated operation and the formula route satisfies that requirement. It's just a matter of trying to eliminate the need for the helper columns. I can live with what I have! Biff "Alan Beban" wrote in message ... If you simply copy the list to another range and then perform Data/Sort, Ascending, it will return black blue grass green green Is that satisfactory? Alan Beban Biff wrote: Hi Domenic! I'm try to extract TEXT values sorted in ascending order. I can do it easily but it takes 2 helper columns. So what I'm trying to do is put it all together in a single formula and eliminate the need for the helpers. The list has dupes and that's what's proving to be a real bear. green black grass blue green I want to extract sorted based on the first letter. It doesn't matter if "black" or "blue" is listed first. So the extracted list would look like this: black blue green green grass Right now I use 2 helpers, 1 returns the code for the first letter: =CODE(UPPER(A1) The other helper is the Rank that break ties: =RANK(B1,B$1:B$5)+COUNTIF(B$1:B1,B1)-1 Then a simple INDEX/MATCH. Trying to put that all together in one formula! Good challenge for someone! Biff "Domenic" wrote in message ... Hi Biff! Unfortunately, I have no idea. Does the solution have to involve the RANK function or are you willing to use another alternative to get ranking? In article , "Biff" wrote: Hi Folks! Anyone know how to pass an array of values as the ref argument of the Rank function without hardcoding or using a range reference? Assume I have this array of values generated by another formula: {71;66;83;71;84} How do I pass that array to Rank? I can get the array passed but then the formula #VALUE! errors: =RANK(71,{71;66;83;71;84}) I've tried using a name for the array, Indexing, Indirect ??? I've never seen this done but I'm not certain that it can't be done. Biff |
|
#6
|
|||
|
|||
|
Would this "auto sort" *array* formula of Harlan's be of any help?
=INDEX($D$1:$D$10,MATCH(SMALL(COUNTIF($D$1:$D$10," <"&$D$1:$D$10),ROW()-ROW($ E$1)+1),COUNTIF($D$1:$D$10,"<"&$D$1:$D$10),0)) The "E1" is not a typo! It's the first cell that you enter the formula in. This works for *all* text, OR *all* numbers. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Biff" wrote in message ... That could be done but it would have to done every time the data changes. A macro could also be used but the macro would have to run every time the data changes. I'm looking for a completely automated operation and the formula route satisfies that requirement. It's just a matter of trying to eliminate the need for the helper columns. I can live with what I have! Biff "Alan Beban" wrote in message ... If you simply copy the list to another range and then perform Data/Sort, Ascending, it will return black blue grass green green Is that satisfactory? Alan Beban Biff wrote: Hi Domenic! I'm try to extract TEXT values sorted in ascending order. I can do it easily but it takes 2 helper columns. So what I'm trying to do is put it all together in a single formula and eliminate the need for the helpers. The list has dupes and that's what's proving to be a real bear. green black grass blue green I want to extract sorted based on the first letter. It doesn't matter if "black" or "blue" is listed first. So the extracted list would look like this: black blue green green grass Right now I use 2 helpers, 1 returns the code for the first letter: =CODE(UPPER(A1) The other helper is the Rank that break ties: =RANK(B1,B$1:B$5)+COUNTIF(B$1:B1,B1)-1 Then a simple INDEX/MATCH. Trying to put that all together in one formula! Good challenge for someone! Biff "Domenic" wrote in message ... Hi Biff! Unfortunately, I have no idea. Does the solution have to involve the RANK function or are you willing to use another alternative to get ranking? In article , "Biff" wrote: Hi Folks! Anyone know how to pass an array of values as the ref argument of the Rank function without hardcoding or using a range reference? Assume I have this array of values generated by another formula: {71;66;83;71;84} How do I pass that array to Rank? I can get the array passed but then the formula #VALUE! errors: =RANK(71,{71;66;83;71;84}) I've tried using a name for the array, Indexing, Indirect ??? I've never seen this done but I'm not certain that it can't be done. Biff |
|
#7
|
|||
|
|||
|
Biff,
Assuming that A1:A5 contains... green black grass blue green B1, copied down: =INDEX($A$1:$A$5,MATCH(SMALL((COUNTIF($A$1:$A$5,"< "&$A$1:$A$5)+1)-ROW($A$ 1:$A$5)/10^10,ROWS($B$1:B1)),(COUNTIF($A$1:$A$5,"<"&$A$1:$ A$5)+1)-ROW($A$ 1:$A$5)/10^10,0)) ....confirmed with CONTROL+SHIFT+ENTER, which will give you the following... black blue grass green green Hope this helps! P.S. As I said before, I do like a challenge. Actually, I surprised myself with this one. :) In article , "Biff" wrote: Hi Domenic! I'm try to extract TEXT values sorted in ascending order. I can do it easily but it takes 2 helper columns. So what I'm trying to do is put it all together in a single formula and eliminate the need for the helpers. The list has dupes and that's what's proving to be a real bear. green black grass blue green I want to extract sorted based on the first letter. It doesn't matter if "black" or "blue" is listed first. So the extracted list would look like this: black blue green green grass Right now I use 2 helpers, 1 returns the code for the first letter: =CODE(UPPER(A1) The other helper is the Rank that break ties: =RANK(B1,B$1:B$5)+COUNTIF(B$1:B1,B1)-1 Then a simple INDEX/MATCH. Trying to put that all together in one formula! Good challenge for someone! Biff "Domenic" wrote in message ... Hi Biff! Unfortunately, I have no idea. Does the solution have to involve the RANK function or are you willing to use another alternative to get ranking? In article , "Biff" wrote: Hi Folks! Anyone know how to pass an array of values as the ref argument of the Rank function without hardcoding or using a range reference? Assume I have this array of values generated by another formula: {71;66;83;71;84} How do I pass that array to Rank? I can get the array passed but then the formula #VALUE! errors: =RANK(71,{71;66;83;71;84}) I've tried using a name for the array, Indexing, Indirect ??? I've never seen this done but I'm not certain that it can't be done. Biff |
|
#8
|
|||
|
|||
|
Very nice, Domenic!
I wan't too far off. Instead of subtracting: ROW($A$1:$A$5)/10^10 I was trying to add: ROW()/10^5 I'll put this one in my "stash". Thanks Biff "Domenic" wrote in message ... Biff, Assuming that A1:A5 contains... green black grass blue green B1, copied down: =INDEX($A$1:$A$5,MATCH(SMALL((COUNTIF($A$1:$A$5,"< "&$A$1:$A$5)+1)-ROW($A$ 1:$A$5)/10^10,ROWS($B$1:B1)),(COUNTIF($A$1:$A$5,"<"&$A$1:$ A$5)+1)-ROW($A$ 1:$A$5)/10^10,0)) ...confirmed with CONTROL+SHIFT+ENTER, which will give you the following... black blue grass green green Hope this helps! P.S. As I said before, I do like a challenge. Actually, I surprised myself with this one. :) In article , "Biff" wrote: Hi Domenic! I'm try to extract TEXT values sorted in ascending order. I can do it easily but it takes 2 helper columns. So what I'm trying to do is put it all together in a single formula and eliminate the need for the helpers. The list has dupes and that's what's proving to be a real bear. green black grass blue green I want to extract sorted based on the first letter. It doesn't matter if "black" or "blue" is listed first. So the extracted list would look like this: black blue green green grass Right now I use 2 helpers, 1 returns the code for the first letter: =CODE(UPPER(A1) The other helper is the Rank that break ties: =RANK(B1,B$1:B$5)+COUNTIF(B$1:B1,B1)-1 Then a simple INDEX/MATCH. Trying to put that all together in one formula! Good challenge for someone! Biff "Domenic" wrote in message ... Hi Biff! Unfortunately, I have no idea. Does the solution have to involve the RANK function or are you willing to use another alternative to get ranking? In article , "Biff" wrote: Hi Folks! Anyone know how to pass an array of values as the ref argument of the Rank function without hardcoding or using a range reference? Assume I have this array of values generated by another formula: {71;66;83;71;84} How do I pass that array to Rank? I can get the array passed but then the formula #VALUE! errors: =RANK(71,{71;66;83;71;84}) I've tried using a name for the array, Indexing, Indirect ??? I've never seen this done but I'm not certain that it can't be done. Biff |
|
#9
|
|||
|
|||
|
Just to clarify, as RagDyer has already shown, this part...
ROW($A$1:$A$5)/10^10 ....isn't necessary, unless you also want to return a corresponding value. So, for example, if A1:B5 contains... green.....75 black.....25 grass.....80 blue.....55 green.....60 D1, copied down and over to the next column... =INDEX(A$1:A$5,MATCH(SMALL((COUNTIF($A$1:$A$5,"<"& $A$1:$A$5)+1)+ROW($A$1: $A$5)/10^10,ROWS(D$1:D1)),(COUNTIF($A$1:$A$5,"<"&$A$1:$A $5)+1)+ROW($A$1:$ A$5)/10^10,0)) ....confirmed with CONTROL+SHIFT+ENTER, would return the following... black.....25 blue.....55 grass.....80 green.....75 green.....60 Notice that in this case I used +ROW(...)/10^10 instead of -ROW(...)/10^10 so that the first occurrence is returned first and the second occurrence second. Now I'll have to put this one in my 'stash' too. :) In article , "Biff" wrote: I wan't too far off. Instead of subtracting: ROW($A$1:$A$5)/10^10 I was trying to add: ROW()/10^5 I'll put this one in my "stash". Thanks Biff |
|
#10
|
|||
|
|||
|
Hi Biff:
If I understand your problem correctly, RANK (for some reason) doesn't work with literal arrays, only with range references that translate to arrays, despite what the Help files say. Regards, Vasant "Biff" wrote in message ... Hi Folks! Anyone know how to pass an array of values as the ref argument of the Rank function without hardcoding or using a range reference? Assume I have this array of values generated by another formula: {71;66;83;71;84} How do I pass that array to Rank? I can get the array passed but then the formula #VALUE! errors: =RANK(71,{71;66;83;71;84}) I've tried using a name for the array, Indexing, Indirect ??? I've never seen this done but I'm not certain that it can't be done. Biff |
|
#11
|
|||
|
|||
|
Hi Vasant!
That's pretty much the conclusion I've come to myself. From help: Ref is an array of, or a reference to, a list of numbers. Nonnumeric values in ref are ignored. What I've been trying to do is build the "is an array of", but it ain't workin'. Biff "Vasant Nanavati" <vasantn AT aol DOT com wrote in message ... Hi Biff: If I understand your problem correctly, RANK (for some reason) doesn't work with literal arrays, only with range references that translate to arrays, despite what the Help files say. Regards, Vasant "Biff" wrote in message ... Hi Folks! Anyone know how to pass an array of values as the ref argument of the Rank function without hardcoding or using a range reference? Assume I have this array of values generated by another formula: {71;66;83;71;84} How do I pass that array to Rank? I can get the array passed but then the formula #VALUE! errors: =RANK(71,{71;66;83;71;84}) I've tried using a name for the array, Indexing, Indirect ??? I've never seen this done but I'm not certain that it can't be done. Biff |
|
#12
|
|||
|
|||
|
"Biff" wrote in message
... Ref is an array of, or a reference to, a list of numbers. Nonnumeric values in ref are ignored. Sadly, the first part is incorrect. That's pretty much the conclusion I've come to myself. Been there, done that! http://groups-beta.google.com/group/...09f686a6e4447/ Regards, Vasant |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
| Where is the bug in my array? | Excel Discussion (Misc queries) | |||
| Help with array formula | Excel Worksheet Functions | |||
| Formula to list unique values | Excel Worksheet Functions | |||
| VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) | |||
Hybrid Mode
