Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Microsoft Visual Basic errors displaid when opening Microsoft Word 97 & Excel (7 | Setting up and Configuration of Excel | |||
Microsoft Excel 1997. How do I sort a protected worksheet? | Excel Discussion (Misc queries) | |||
RANK, duplicate ranking but no gaps in rank | Excel Worksheet Functions | |||
Change individual cell heights/widths in Microsoft Excel 2000 like Microsoft Word | Excel Discussion (Misc queries) | |||
Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) |