Thursday, August 11, 2011

yogi_Compute Rank Based On Values In A Row Of Columns In A Specified Pattern

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
gookon said ...
Hello all.I'm having some trouble using the Rank and Offset functions and hoped someone could point me in the right direction.In the following sample for example, in C4 I am trying to enter the rank of B4 from B4,E4,I4,etc. consistently offsetting 3 cells to the right.In F4 I want to enter the rank of E4 from the same cells as C4 and so on.
A B C D E F G H I and so on....
1| 0 0 3 3 0 0
2| 3 3 3 6 1 1
3| 0 3 0 6 1 2
4| 1 4 1 7 3 5
I've been playing with variations of =Rank(B4,Offset(B4,0,3) but keep getting errors and I'm not honestly even sure if this is the best way to achieve what I want.
Any help at all would be greatly appreciated
-------------------------------------------------------
If understood your intent correctly, I propose a little change in your setup as shown in the following proposed solution ...