Microsoft Excel Rank and Sort
Jerry,
One way is as follows:
in cell K6 =RANK(K5,$K$5:$N$5,0)
The last entry is for order 0 = decreasing and 1 = increasing.
Copy this across to the other 3 cells. (Make sure the $ are in the same.)
This finds the order.
Then in cell C2 enter:
=OFFSET($J2,0,MATCH(COLUMNS($C$1:C1),$K$6:$N$6,0))
Copy this across and down to fill the area $C$2:$f$6.
This picks up the cells in the range based on the rank.
the columns() gives a count of the columns.
if to get the posistion 1,2,3..
It then matches the count ie a running sequence with the ranked numbers
to get the column with the sequence.
this is then used in the offset selection for the new data.
The offset uses a base cell and then moves right (or left if -ve) and down
(or up if -ve).
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.
" wrote:
I have data that I would like to rank and sort by variance of forecast
minus actual
K2-N2 are locations Dallas, Houston, Baton Rouge, New Orleans.
K3-N3 is actual data values of 56.50, 49.60, 47.50 and 51.80
K4-N4 is budget data values of 47.70, 33.70, 31.30 and 42.58
K5-N5 is formula of row 4 minus row 3 (example =k4-k3)
K6-N6 need to have rank of values in row 5, going from 1 thru 4
I would like the data ranked and then displayed in the same rows in
Columns C-F
I want to use formulas to do this because this will be updated
monthly, and i am not very knowledgeable in Macros. Will provide a
example if it will help.
Thanks.
|