I have 4 worksheets, named Apples, Pears, Bananas, Oranges (in that order in the workbook) with tables of data in them. The data is arranged in exactly the same format. I wish to rank the number of apples in each cell of the sheet "Apples" against the numbers in the same cells on the sheets for the other fruits. How do I do this? My attempt is to create a new worksheet (so that I avoid circullar references) called "Ranking" and enter:
=RANK(Apples!B10,Pears:Oranges!B10,1).
However this produces a #N/A error. Any ideas?
=RANK(Apples!B10,Pears:Oranges!B10,1) works perfectly fine for me as long as the Apples sheet is included in the Pears:Oranges sheet range as the Rank function will only rank elements that are INCLUDED in the ref Range. Otherwise the formula will result in #N/A.
So make sure you have the Apples sheet included in the Pears:Oranges sheet range, if that was not the case yet expand into:
=RANK(Apples!B10,Apples:Oranges!B10,1)