Rank with array formulae 1

[ScorchedLemonade]

I'm trying to do something of the form

=RANK(B263/C263,IF(ISERROR(B3:B263/C3:C263),"",B3:B263/C3:C263),1)

with an array formula but I get the #VALUE! error.

I assume that the problem is with the second part as I can put =B263/C263 in another cell and substitute that with no improvement. There would be many such arrays in the sheet so I was hoping to avoid using many cells to actual produce the arrays like B3:B263/C3:C263 (e.g. in D3263) and then =rank(B263/C263,D3263,1).

Am I being too hopeful in thinking that perhaps this can be done?

Thanks,

Richard.

 

[GlennUK]

You can't use the Rank function on array data.

You're ranking a value ( ascending order ), so maybe you could do something else to calculate the rank, like ...

=SUM(IF(B263/C263>B3:B263/C3:C263,1,0))+1

entered as an array formula ( Ctrl-Shift-Enter ).

This counts all the instances that are less than the number being tested, so the rank would be one more than that.

Cheers, Glenn.

 

[ScorchedLemonade]

I feel rather dumb at not having thought of the suggested solution, which works very well (though I had to amend a little part of it as some parts contain text (of the #N/A ... type)

SUM(IF(ISERROR(B263/C263+B3:B263/C3:C263),0,IF(B263/C263>B3:B263/C3:C263,1,0)))+1

), but in any event the first piece of information was very useful too.

Plus, a quick solution again.

Thanks,

Richard.