0
0
2
0
1
1
43
23
11
8
0
0
1
don’t count if it’s <2
Hallo
=IF(A1>1;RANK(A1;IF(A$1:A$12>1;A$1:A$12;""));"")
I didn’t know that you can compare a range and it will auto-align a pulled out of the range value in a relative way for a current copied cell when copying, without specifying it… Well that’s some weird magic A$1:A$12>1.
Even though I was thinking more of a as a single cell without copying.
It’s called implicit intersection. For function parameters that expect a single scalar value as argument if given a cell range single column or row vector, the value of the intersection of that vector and the formula cell position is taken.
I don’t think that it works actually. It just cuts the numbers on the first check “A1>1”, rather than feeding it a range without 0s and 1s, which is what I want. It’s more obvious with PERCENTRANK.
After having entered the formula in B1 you need to pull/copy-paste the formula cell down until the end of the data range, e.g. to B12.
I believe that not the Value should be checked for >= 2, but Data should be filtered, per OP question.
Something like
RANK(A1; A$1:A$12 * (A$1:A$12 >= 2))
(I can’t test at the moment, and suppose that my formula is incorrect, since it would still count everything below 2 as 0)
you need to check the [x]matrix-option in Formula_wizard… AND hold the <ctrl> key meanwhile pulling down.