Microsoft Excel Rank and Sort

[jerrymoon1]

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.

 

[Guest]

Try this in K6

=RANK(K5,$K5:$N5,1)

and copy to N6

If you want it ranked in descending order, change the 1 to a 0.

HTH,
Barb Reinhardt

 

[Guest]

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).