Excel Rank syntax

[Guest]

I would setup conditional format by using Rank function, but the REF value
still needs another calcualtion before it can be put into the function. I
tried to write like this,

=rank(a1/b1,(a$1:$3/b$1:b$3)) or =rank(a1/b1,(a$1/b$1,a$2/b$2,a$3/b$3))

They were not acceptable with an alert window saying regular mistakes.
My question is, is the cell reference the only thing in the REF than any
other values?? Can I just write values for the REF like I did, or I have to
occupy more cells to keep median values?? Thanks!!

 

[Aladin Akyurek]

The evaluation of A$1:A$3/B$1:B$3 is an array object. RANK() does not
admit such objects. It's certainly more efficient to create an
additional range with the required division and apply RANK to that
range. if you insist on not creating an additional range, you'll need
something like:

=SUMPRODUCT(--($A$2:$A$3/$B$2:$B$3>A2/B2))+1