Microsoft Excel Rank and Sort
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. |
Microsoft Excel Rank and Sort
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 " 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. |
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. |
All times are GMT +1. The time now is 11:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com