(Edited for better readabiliy; Lupp)
(The formula below should be entered for evaluation in array-mode.)
=IF(JC11=0;"";RANK(JC11;IF($JC$11:$JC$100>0;$JC$11:$JC$100;1))+COUNTIF($JC$11:$JC$11;$JC$100)-0)
I found this on the net, works great. One thing, I need it reverse order
show smallest to equal “1” not max equal “1”.
where do I put the “,1” or how can it be changed to show Rank smallest to max
(i’ve tried several scenario’s nothing seems to work)
thank you in advance for any help provided.
Did you read the help text? Here it is:
RANK
Returns the rank of a number in a sample.
Syntax
RANK(Value; Data; Type)
Value is the value, whose rank is to be determined.
Data is the array or range of data in the sample.
Type (optional) is the sequence order.
Type = 0 means descending from the last item of the array to the first (this is the default),
Type = 1 means ascending from the first item of the range to the last.
Example
=RANK(A10;A1:A50) returns the ranking of the value in A10 in value range A1:A50. If Value does not exist within the range an error message is displayed.
The third parameter position of RANK in your example is behind the closing parenthesis of the inner IF-function call.
What shall the “-0” at the end achieve?
I do not yet understand “…show smallest to equal ‘1’ not max equal ‘1’”.
As far as I can see your formula can only make sense if evaluated in array-mode. You have to enter it with Ctrl+Shift+Enter. It will then be displayed enclosed in curly brackets.
(Edited regarding the recent comments)
I know do less than ever understand the original topic clearly suggesting it was a matter of ascending/descending.
Some basics. RANK has one ability making it the function of choice in very specific cases: It does accept ‘NumberSequenceList’ for the passing of data. You don’t know the meaning? Don’t worry! This just confirms my assumption you wouldn’t need RANK at all. There are more shortcomings of RANK than advantages as compared with alternative solutions. As long as your data to rank values with respect to are given referencing a simple array, you should better use a formula counting the higher/lower rating values to get the correct rank. Using SUMPRODUCT for the purpose will offer you proper means to implement any special behaviour you want.
See this attached example for more detail.
I understand what you are saying. and reviewed all publications. This is why I am asking. How I would write it. I’ve tried several methods with no success.
I simply am not sure about your intentions. To type a “1” in the place of a formerly omitted third parameter will surely not be your problem. But what is it? The formula didn’t yet tell me for what purpose it might be designed. It’s cryptic to me and I cannot reasonably guess therefore.
I also am irritated by your statement that you “reviewed all (the) publications”. Why - an what publications?
Please try to tell in clear words what you want to achieve.
What did you ask/search for when you were on the way to “find the formula on the net”?
Where did you find it? The URL might help to understand.
ok, to make it simpler how do I write a Rank to exclude zero’s in columns =IFERROR(RANK(H632,$H$570:$H$633),"")
this will not put a RANK in a row where there’s “no data” but if I have zero’s how to fix?