One thumb rule while computing always prevails,that whenever user needs to perform calculation which has either series of values or one value is suppose to generate many intermediate values, for this an object or a Data handler is required, which has Elements as many as Values, is basically called elements of the Variable or an ARRAY.
Every programming language provides command to declare Variable and an Array of Variable.
For example in Visual Basic,
Dim MyArray(2) as String
MyArray(0) = "A"
MyArray(1) = "AB"
We can use these elements if & when required as their Data type.
Now returning to Excel, here also fundamentals are similar. But Excel doesn't allows to declare Variable or an Array. Therefore Cell address can be used as elements either horizontally or vertically.
like simply write 5,6,7,8 & 9 in E1,F1,G1,H1 & I1. And write this CSE Formula in E3, {=SUM(E1:I1*{1,2,3,4,5})}, You get answer 115.
Excel calculates like E1*1 + F1*2 + G1*3 + H1*4 + I1*5 produce 5, 12, 21, 32, 45 equal to 115.
TRANSPOSE is always an CSE formula.
So the conclusion is whenever any such calculation is required Array Formula can be used. Popularly in Excel we call it CSE (Control+Shift+Enter) Formula.
I do hope above written text help you to decide when an Array Formula is required.
Also, post your Rank Formula with related data you are struggling with I'll try to simplify it.
I happened to run across this problem with RANK() recently. It will take 2 arrays as arguments, but only one of them can be calculated inside the function. e.g. RANK(2A1:A5,B1:B5,0) works correctly, but RANK(2A1:A5,2*B1:B5,0) gives an error. – Bandersnatch – 2017-11-14T16:18:31.343
I know that doesn't answer your question, but it might help you use Rank() effectively. I, too, have tried to find an exhaustive reference for functions that can use arrays, but no luck so far. – Bandersnatch – 2017-11-14T16:29:51.820