Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Rank in an Array Formula
Hi,
I'm using the Rank function in an Array Formula, but after several tries I always got #VALUE! Error. What I'm trying to get is the rank of a value (e.g. 4) in subset of a list of values (e.g. {4;3;2;1} where A1:A7="a"). A B 1 a 4 2 a 3 3 a 2 4 a 1 5 b 9 6 b 8 7 b 7 ={RANK(B2,IF(A1=A1:A7,B1:B7),0)} = #VALUE! Anyone as a hint to this problem? Thanks in advance, Ricardo Dinsi |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Rank in an Array Formula
Another approach without an array:-
With your data in A1 - B7 as below put this in C1 =SUMPRODUCT(--($A$1:$A$7=A1),--(B1<$B$1:$B$7))+1 The formula will give the rank of B1 for the group in A1 The formula is dragable Mike "Ricardo Dinis" wrote: Hi, I'm using the Rank function in an Array Formula, but after several tries I always got #VALUE! Error. What I'm trying to get is the rank of a value (e.g. 4) in subset of a list of values (e.g. {4;3;2;1} where A1:A7="a"). A B 1 a 4 2 a 3 3 a 2 4 a 1 5 b 9 6 b 8 7 b 7 ={RANK(B2,IF(A1=A1:A7,B1:B7),0)} = #VALUE! Anyone as a hint to this problem? Thanks in advance, Ricardo Dinsi |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Rank in an Array Formula
On 10 Dez, 13:29, Mike H wrote:
Another approach without an array:- With your data in A1 - B7 as below put this in C1 =SUMPRODUCT(--($A$1:$A$7=A1),--(B1<$B$1:$B$7))+1 The formula will give the rank of B1 for the group in A1 The formula is dragable Mike "Ricardo Dinis" wrote: Hi, I'm using the Rank function in an Array Formula, but after several tries I always got #VALUE! Error. What I'm trying to get is the rank of a value (e.g. 4) in subset of a list of values (e.g. {4;3;2;1} where A1:A7="a"). A B 1 a 4 2 a 3 3 a 2 4 a 1 5 b 9 6 b 8 7 b 7 ={RANK(B2,IF(A1=A1:A7,B1:B7),0)} = #VALUE! Anyone as a hint to this problem? Thanks in advance, Ricardo Dinsi It works fine. Thank you very much. Can you explain me what does the '--' operator before conditions or give me a link? I google it and i can't find it :( |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Rank in an Array Formula
Ricardo,
I'm glad it solved your problem, thanks for the feedback. The -- or double unary converts true/false to 1/0 to see it in action enter this formula =(--(A1="Dog")) will return 0 unless A1 contaiins Dog For a more professional explanation have a look here http://www.xldynamic.com/source/xld.SUMPRODUCT.html Mike "Ricardo Dinis" wrote: On 10 Dez, 13:29, Mike H wrote: Another approach without an array:- With your data in A1 - B7 as below put this in C1 =SUMPRODUCT(--($A$1:$A$7=A1),--(B1<$B$1:$B$7))+1 The formula will give the rank of B1 for the group in A1 The formula is dragable Mike "Ricardo Dinis" wrote: Hi, I'm using the Rank function in an Array Formula, but after several tries I always got #VALUE! Error. What I'm trying to get is the rank of a value (e.g. 4) in subset of a list of values (e.g. {4;3;2;1} where A1:A7="a"). A B 1 a 4 2 a 3 3 a 2 4 a 1 5 b 9 6 b 8 7 b 7 ={RANK(B2,IF(A1=A1:A7,B1:B7),0)} = #VALUE! Anyone as a hint to this problem? Thanks in advance, Ricardo Dinsi It works fine. Thank you very much. Can you explain me what does the '--' operator before conditions or give me a link? I google it and i can't find it :( |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Rank in an Array Formula
Converts Boolean constants (TRUE, FALSE) to numbers (1,0)
Have a look at: J.E McGimpsey's site http://mcgimpsey.com/excel/formulae/doubleneg.html It is odd that the formula =RANK(3,{1;2;3;4;5}) fails when Help states that RANK works with an array or reference to an list of numbers best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Ricardo Dinis" wrote in message ... On 10 Dez, 13:29, Mike H wrote: Another approach without an array:- With your data in A1 - B7 as below put this in C1 =SUMPRODUCT(--($A$1:$A$7=A1),--(B1<$B$1:$B$7))+1 The formula will give the rank of B1 for the group in A1 The formula is dragable Mike "Ricardo Dinis" wrote: Hi, I'm using the Rank function in an Array Formula, but after several tries I always got #VALUE! Error. What I'm trying to get is the rank of a value (e.g. 4) in subset of a list of values (e.g. {4;3;2;1} where A1:A7="a"). A B 1 a 4 2 a 3 3 a 2 4 a 1 5 b 9 6 b 8 7 b 7 ={RANK(B2,IF(A1=A1:A7,B1:B7),0)} = #VALUE! Anyone as a hint to this problem? Thanks in advance, Ricardo Dinsi It works fine. Thank you very much. Can you explain me what does the '--' operator before conditions or give me a link? I google it and i can't find it :( |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Rank in an Array Formula
On 10 Dez, 14:20, "Bernard Liengme"
wrote: Converts Boolean constants (TRUE, FALSE) to numbers (1,0) Have a look at: J.E McGimpsey's sitehttp://mcgimpsey.com/excel/formulae/doubleneg.html It is odd that the formula =RANK(3,{1;2;3;4;5}) fails when Help states that RANK works with an array or reference to an list of numbers best wishes -- Bernard V Liengme Microsoft Excel MVPwww.stfx.ca/people/bliengme remove caps from email "Ricardo Dinis" wrote in message ... On 10 Dez, 13:29, Mike H wrote: Another approach without an array:- With your data in A1 - B7 as below put this in C1 =SUMPRODUCT(--($A$1:$A$7=A1),--(B1<$B$1:$B$7))+1 The formula will give the rank of B1 for the group in A1 The formula is dragable Mike "Ricardo Dinis" wrote: Hi, I'm using the Rank function in an Array Formula, but after several tries I always got #VALUE! Error. What I'm trying to get is the rank of a value (e.g. 4) in subset of a list of values (e.g. {4;3;2;1} where A1:A7="a"). A B 1 a 4 2 a 3 3 a 2 4 a 1 5 b 9 6 b 8 7 b 7 ={RANK(B2,IF(A1=A1:A7,B1:B7),0)} = #VALUE! Anyone as a hint to this problem? Thanks in advance, Ricardo Dinsi It works fine. Thank you very much. Can you explain me what does the '--' operator before conditions or give me a link? I google it and i can't find it :( Thanks. It'll be very handy! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Rank in an Array Formula
On 10 Dec, 13:29, Mike H wrote:
Another approach without anarray:- With your data in A1 - B7 as below put this in C1 =SUMPRODUCT(--($A$1:$A$7=A1),--(B1<$B$1:$B$7))+1 Theformulawill give therankof B1 for the group in A1 Theformulais dragable Mike "Ricardo Dinis" wrote: Hi, I'm using theRankfunction in anArrayFormula, but after several tries I always got #VALUE! Error. What I'm trying to get is therankof a value (e.g. 4) in subset of a list of values (e.g. {4;3;2;1} where A1:A7="a"). A B 1 a 4 2 a 3 3 a 2 4 a 1 5 b 9 6 b 8 7 b 7 ={RANK(B2,IF(A1=A1:A7,B1:B7),0)} = #VALUE! Anyone as a hint to this problem? Thanks in advance, Ricardo Dinsi- Hide quoted text - - Show quoted text - What if you have two entries the same at equal posistions, can you add a bit to make the first in the list one and the other second? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Rank in an Array Formula
On 11 Dec, 12:50, "
wrote: On 10 Dec, 13:29, Mike H wrote: Another approach without anarray:- With your data in A1 - B7 as below put this in C1 =SUMPRODUCT(--($A$1:$A$7=A1),--(B1<$B$1:$B$7))+1 Theformulawill give therankof B1 for the group in A1 Theformulais dragable Mike "Ricardo Dinis" wrote: Hi, I'm using theRankfunction in anArrayFormula, but after several tries I always got #VALUE! Error. What I'm trying to get is therankof a value (e.g. 4) in subset of a list of values (e.g. {4;3;2;1} where A1:A7="a"). A B 1 a 4 2 a 3 3 a 2 4 a 1 5 b 9 6 b 8 7 b 7 ={RANK(B2,IF(A1=A1:A7,B1:B7),0)} = #VALUE! Anyone as a hint to this problem? Thanks in advance, Ricardo Dinsi- Hide quoted text - - Show quoted text - What if you have two entries the same at equal posistions, can you add a bit to make the first in the list one and the other second?- Hide quoted text - - Show quoted text - Sorry, that isn't very cear. If it ends up the that there are two values the same, they will have equal rank. Can the formula be made to stop this from happening? So if there are two entries that are 3rd, one is made to be 4th? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
include criteria to 'rank based array function' | Excel Discussion (Misc queries) | |||
rank based array function | Excel Discussion (Misc queries) | |||
Rank with an array formula | Excel Worksheet Functions | |||
Excel CHALLENGE...3 questions(indirect,rank,array formula)... | Excel Worksheet Functions | |||
Pass an array to Rank | Excel Worksheet Functions |