Rank() function Across worksheets

[David]

Hi,
Does anyone out there know if it is possible to rank
information across separate sheets in the same workbook?
I can get Rank() to work only in the first sheet,
multiple sheet references have a syntax problem in the
function.
(I can get averages and SDEV to work across the required
sheets)
thanks
David

 

[Frank Kabel]

Hi as a workaround try something like the following
1. Download the free add-in Morefunc.xll
(http://longre.free.fr/english). The function THREED converts a 3D
array to a 2D array.

2. Now use the follwoing formula to get the ranke of the cell
'sheet1'!A2
=SUMPRODUCT(--(THREED('sheet1:sheet5'!A1:A10)<'sheet1'!A2))+1

 

[Guest]

Thank you Frank,
I'll have to download at home as the system is locked by
the tech staff here at work.
Does this software work across different references in the
other sheets?
Regards
David

 

[Frank Kabel]

Hi
what dou you mean with 'different references'. If you mean different
cell ranges for each sheet: No