Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank and Sort
On sheet 1 in column A is the name of the account; same sheet column B is a
percentage. Account Percentage Mercy 80.4% Henry 89.6% Fred 0.00% Chris 92.70% Connie 92.00% Alex 92.60% Larry 89.80% Ollie 0.00% On a second sheet (a summary sheet) a formula is needed to sort and rank the accounts so that the account with the highest percentage is at the top of the list, like this, Chris 92.70% Alex 92.60% Connie 92.00% etc I have tried a combination of formulas but have been unsuccessful. Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank and Sort
On sheet1, create a helper column (I'll assume column C), with this formula
entered in row 2 and copied down: =RANK(B2,$B$2:$B$6)+IF(COUNTIF($C$1:C1,RANK(B2,$B$ 2:$B$6))0,1,0) On sheet2, A2 formula is: =INDEX(Sheet1!A$2:A$100,MATCH(ROW(Sheet1!$A1),Shee t1!$C$2:$C$100,0)) Copy down as needed, and across 1 column. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Basenji" wrote: On sheet 1 in column A is the name of the account; same sheet column B is a percentage. Account Percentage Mercy 80.4% Henry 89.6% Fred 0.00% Chris 92.70% Connie 92.00% Alex 92.60% Larry 89.80% Ollie 0.00% On a second sheet (a summary sheet) a formula is needed to sort and rank the accounts so that the account with the highest percentage is at the top of the list, like this, Chris 92.70% Alex 92.60% Connie 92.00% etc I have tried a combination of formulas but have been unsuccessful. Thank you. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank and Sort
On the same sheet for simplification, with your data in a2:b9, with
the numbers 1,2,3 in cells e2, e3, e4: I got your top 3 scorers in column G with =LARGE($B$2:$B$9,E2) then in column F i picked up the corresponding names with =OFFSET($A$1,MATCH(G2,$B$2:$B$9,FALSE),0) The formula will be more complicated if you have to allow for ties. If you copy the two formulas down 8 rows and fill in the sequence 1,2,...8 in column E you will see that Fred shows up for both zeros. Good luck Ken Norfolk, Va On Jul 17, 2:53*pm, Basenji wrote: On sheet 1 in column A is the name of the account; same sheet column B is a percentage. Account * * Percentage Mercy * 80.4% Henry * 89.6% Fred * *0.00% Chris * 92.70% Connie *92.00% Alex * *92.60% Larry * 89.80% Ollie * 0.00% On a second sheet (a summary sheet) a formula is needed to sort and rank the accounts so that the account with the highest percentage is at the top of the list, like this, Chris * * * * 92.70% Alex * * * * *92.60% Connie * * * 92.00% etc I have tried a combination of formulas but have been unsuccessful. Thank you. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank and Sort
Try this...
Data in the range Sheet1A2:B9 Array entered** on Sheet2 in cell A2: =INDEX(Sheet1!A$2:A$9,MATCH(LARGE(Sheet1!$B$2:$B$9-ROW(Sheet1!B$2:B$9)/10^10,ROWS(A$2:A2)),Sheet1!$B$2:$B$9-ROW(Sheet1!B$2:B$9)/10^10,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy across to B2 then down to A9:B9. Accounts for possible ties. -- Biff Microsoft Excel MVP "Basenji" wrote in message ... On sheet 1 in column A is the name of the account; same sheet column B is a percentage. Account Percentage Mercy 80.4% Henry 89.6% Fred 0.00% Chris 92.70% Connie 92.00% Alex 92.60% Larry 89.80% Ollie 0.00% On a second sheet (a summary sheet) a formula is needed to sort and rank the accounts so that the account with the highest percentage is at the top of the list, like this, Chris 92.70% Alex 92.60% Connie 92.00% etc I have tried a combination of formulas but have been unsuccessful. Thank you. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank and Sort
Impressive formula. Thank you. Now three questions:
1. Does Rows(A$2:A2) refer to sheet 1 or sheet 2 or does it make any difference? 2. What does 10^10 mean? 3. Will this array formula work if the column for the accounts and the percentages are in nonadjacent columns on sheet 1, such as the name of the account in column A and the percentage in column C? The percentages in column B are for January; the percentages in column C are for February. The same rank and sort is needed for the percentages in column C and subsequent columns so that one can quickly see which account is at the top for each month. Or does the account name have to be adjacent to the percentage for the array formula to function properly? Thank you. "T. Valko" wrote: Try this... Data in the range Sheet1A2:B9 Array entered** on Sheet2 in cell A2: =INDEX(Sheet1!A$2:A$9,MATCH(LARGE(Sheet1!$B$2:$B$9-ROW(Sheet1!B$2:B$9)/10^10,ROWS(A$2:A2)),Sheet1!$B$2:$B$9-ROW(Sheet1!B$2:B$9)/10^10,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy across to B2 then down to A9:B9. Accounts for possible ties. -- Biff Microsoft Excel MVP "Basenji" wrote in message ... On sheet 1 in column A is the name of the account; same sheet column B is a percentage. Account Percentage Mercy 80.4% Henry 89.6% Fred 0.00% Chris 92.70% Connie 92.00% Alex 92.60% Larry 89.80% Ollie 0.00% On a second sheet (a summary sheet) a formula is needed to sort and rank the accounts so that the account with the highest percentage is at the top of the list, like this, Chris 92.70% Alex 92.60% Connie 92.00% etc I have tried a combination of formulas but have been unsuccessful. Thank you. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank and Sort
Hello,
If your data is in Sheet1 in cells A2:M1001 (account names in column A, Jan data in column B, Feb data in C, ..., December data in M), then enter into Sheet2: Cell C1: 1 for January (2 for February, ..., 12 for December) Cell A2: =COUNTIF(INDEX(Sheet1!$B$2:$M$1001,,$C$1),""&INDE X(Sheet1!B2:M2,,$C $1))+COUNTIF(INDEX(Sheet1!$B$2:M2,,$C$1),INDEX(She et1!B2:M2,,$C$1)) Cell B2: =INDEX(Sheet1!$A$2:$A$1001,MATCH(ROW()-1,$A$2:$A$1001,0)) Cell C2: =INDEX(INDEX(Sheet1!$B$2:$M$1001,,$C$1),MATCH(ROW( )-1,$A$2:$A$1001,0)) Now copy A2:C2 down to row 1001. This flexible approach is taking care about your data arrangements, it is about 5x faster than the /10^10 one given by Biff, and it can even sort text (not necessary here, but you might need it one day). See http://sulprobil.com/html/sorting.html, please. Regards, Bernd |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank and Sort
1.Does Rows(A$2:A2) refer to sheet 1 or sheet 2
or does it make any difference? Rows(A$2:A2) refers to the first cell the formula is entered in. In this example the first formula was entered in cell A2 hence: Rows(A$2:A2). From a purely technical standpoint, you don't need to include the sheet name in that type of expression. For example, this expression: ROW(Sheet1!B$2:B$9) Can be written as: ROW(B$2:B$9) The sheet name is irrelavent to what the function is doing.. The function is returning the array {2;3;4;5;6;7;8;9}. The function returns the *exact* same array with or without the sheet name: ROW(Sheet1!B$2:B$9) = {2;3;4;5;6;7;8;9} ROW(B$2:B$9) = {2;3;4;5;6;7;8;9} The sheet name is included to reduce confusion and let's the user know that the function is refering to data on the specified sheet. 2.What does 10^10 mean? 10 to the 10th power or 10*10*10*10*10*10*10*10*10*10 or =POWER(10,10) or 10,000,000,000 A typical lookup formula will *always* only find the first instance of the lookup_value and this poses a problem when there are multiple instances of the lookup_value and we need to find all of them. So, we need some method of making every lookup_value unique. Consider this: B2 = 10 B3 = 10 Ok, we have a tie. Here's how we break that tie and make each value unique. B2:B3-ROW(B2:B3)/10^10 cell_value-(row_number/10^10) B2 = 10-(2/10,000,000,000) = 9.9999999998 B3 = 10-(3/10,000,000,000) = 9.9999999997 Now we have all unique numbers to look for! 3.Will this array formula work if the column for the accounts and the percentages are in nonadjacent columns Yes, but then you'd need to use 2 formulas. One for the name and one for the amount. You simply change the the indexed range. With the names in column A and the amounts in column C: For the names: =INDEX(Sheet1!A$2:A$9..... For the amounts: =INDEX(Sheet1!C$2:C$9..... -- Biff Microsoft Excel MVP "Basenji" wrote in message ... Impressive formula. Thank you. Now three questions: 1. Does Rows(A$2:A2) refer to sheet 1 or sheet 2 or does it make any difference? 2. What does 10^10 mean? 3. Will this array formula work if the column for the accounts and the percentages are in nonadjacent columns on sheet 1, such as the name of the account in column A and the percentage in column C? The percentages in column B are for January; the percentages in column C are for February. The same rank and sort is needed for the percentages in column C and subsequent columns so that one can quickly see which account is at the top for each month. Or does the account name have to be adjacent to the percentage for the array formula to function properly? Thank you. "T. Valko" wrote: Try this... Data in the range Sheet1A2:B9 Array entered** on Sheet2 in cell A2: =INDEX(Sheet1!A$2:A$9,MATCH(LARGE(Sheet1!$B$2:$B$9-ROW(Sheet1!B$2:B$9)/10^10,ROWS(A$2:A2)),Sheet1!$B$2:$B$9-ROW(Sheet1!B$2:B$9)/10^10,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy across to B2 then down to A9:B9. Accounts for possible ties. -- Biff Microsoft Excel MVP "Basenji" wrote in message ... On sheet 1 in column A is the name of the account; same sheet column B is a percentage. Account Percentage Mercy 80.4% Henry 89.6% Fred 0.00% Chris 92.70% Connie 92.00% Alex 92.60% Larry 89.80% Ollie 0.00% On a second sheet (a summary sheet) a formula is needed to sort and rank the accounts so that the account with the highest percentage is at the top of the list, like this, Chris 92.70% Alex 92.60% Connie 92.00% etc I have tried a combination of formulas but have been unsuccessful. Thank you. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank and Sort
"T. Valko" wrote: Try this... Data in the range Sheet1A2:B9 Array entered** on Sheet2 in cell A2: =INDEX(Sheet1!A$2:A$9,MATCH(LARGE(Sheet1!$B$2:$B$9-ROW(Sheet1!B$2:B$9)/10^10,ROWS(A$2:A2)),Sheet1!$B$2:$B$9-ROW(Sheet1!B$2:B$9)/10^10,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy across to B2 then down to A9:B9. Accounts for possible ties. -- Biff Microsoft Excel MVP "Basenji" wrote in message ... On sheet 1 in column A is the name of the account; same sheet column B is a percentage. Account Percentage Mercy 80.4% Henry 89.6% Fred 0.00% Chris 92.70% Connie 92.00% Alex 92.60% Larry 89.80% Ollie 0.00% On a second sheet (a summary sheet) a formula is needed to sort and rank the accounts so that the account with the highest percentage is at the top of the list, like this, Chris 92.70% Alex 92.60% Connie 92.00% etc I have tried a combination of formulas but have been unsuccessful. Thank you. Okay, now a variation is needed: same accounts but this time the column of numbers (general numbers and not percentages this time) and accounts need to be ranked and sorted with the low number at the top and any accounts that have zero will be at the bottom of the list. Thus it would look like this. Account Minutes Mercy 42 Henry 45 Fred 53 Chris 59 Connie 65 Alex 0 Larry 0 Ollie 0 In the formula you kindly provided I have changed the LARGE function to SMALL, which puts the numbers in order with the smallest one at the top, which is zero. But the zero value in this case is not good and should be at the bottom. The first number can be anything greater than zero. Would an IF function help solve this? Thank you. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank and Sort
Many thanks for these explanations.
"T. Valko" wrote: 1.Does Rows(A$2:A2) refer to sheet 1 or sheet 2 or does it make any difference? Rows(A$2:A2) refers to the first cell the formula is entered in. In this example the first formula was entered in cell A2 hence: Rows(A$2:A2). From a purely technical standpoint, you don't need to include the sheet name in that type of expression. For example, this expression: ROW(Sheet1!B$2:B$9) Can be written as: ROW(B$2:B$9) The sheet name is irrelavent to what the function is doing.. The function is returning the array {2;3;4;5;6;7;8;9}. The function returns the *exact* same array with or without the sheet name: ROW(Sheet1!B$2:B$9) = {2;3;4;5;6;7;8;9} ROW(B$2:B$9) = {2;3;4;5;6;7;8;9} The sheet name is included to reduce confusion and let's the user know that the function is refering to data on the specified sheet. 2.What does 10^10 mean? 10 to the 10th power or 10*10*10*10*10*10*10*10*10*10 or =POWER(10,10) or 10,000,000,000 A typical lookup formula will *always* only find the first instance of the lookup_value and this poses a problem when there are multiple instances of the lookup_value and we need to find all of them. So, we need some method of making every lookup_value unique. Consider this: B2 = 10 B3 = 10 Ok, we have a tie. Here's how we break that tie and make each value unique. B2:B3-ROW(B2:B3)/10^10 cell_value-(row_number/10^10) B2 = 10-(2/10,000,000,000) = 9.9999999998 B3 = 10-(3/10,000,000,000) = 9.9999999997 Now we have all unique numbers to look for! 3.Will this array formula work if the column for the accounts and the percentages are in nonadjacent columns Yes, but then you'd need to use 2 formulas. One for the name and one for the amount. You simply change the the indexed range. With the names in column A and the amounts in column C: For the names: =INDEX(Sheet1!A$2:A$9..... For the amounts: =INDEX(Sheet1!C$2:C$9..... -- Biff Microsoft Excel MVP "Basenji" wrote in message ... Impressive formula. Thank you. Now three questions: 1. Does Rows(A$2:A2) refer to sheet 1 or sheet 2 or does it make any difference? 2. What does 10^10 mean? 3. Will this array formula work if the column for the accounts and the percentages are in nonadjacent columns on sheet 1, such as the name of the account in column A and the percentage in column C? The percentages in column B are for January; the percentages in column C are for February. The same rank and sort is needed for the percentages in column C and subsequent columns so that one can quickly see which account is at the top for each month. Or does the account name have to be adjacent to the percentage for the array formula to function properly? Thank you. "T. Valko" wrote: Try this... Data in the range Sheet1A2:B9 Array entered** on Sheet2 in cell A2: =INDEX(Sheet1!A$2:A$9,MATCH(LARGE(Sheet1!$B$2:$B$9-ROW(Sheet1!B$2:B$9)/10^10,ROWS(A$2:A2)),Sheet1!$B$2:$B$9-ROW(Sheet1!B$2:B$9)/10^10,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy across to B2 then down to A9:B9. Accounts for possible ties. -- Biff Microsoft Excel MVP "Basenji" wrote in message ... On sheet 1 in column A is the name of the account; same sheet column B is a percentage. Account Percentage Mercy 80.4% Henry 89.6% Fred 0.00% Chris 92.70% Connie 92.00% Alex 92.60% Larry 89.80% Ollie 0.00% On a second sheet (a summary sheet) a formula is needed to sort and rank the accounts so that the account with the highest percentage is at the top of the list, like this, Chris 92.70% Alex 92.60% Connie 92.00% etc I have tried a combination of formulas but have been unsuccessful. Thank you. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank and Sort
Using named ranges...
$A$2:$A$9 = Acc (Account) $B$2:$B$9 = Min (Minutes) Array entered in D2: =INDEX(A$2:A$9,MATCH(SMALL(IF(Min0,Min+ROW(Min)/10^10,MAX(Min)+1+ROW(Min)),ROWS(D$2:D2)),IF(Min0, Min+ROW(Min)/10^10,MAX(Min)+1+ROW(Min)),0)) Copied across to E2 then down to D9:E9 -- Biff Microsoft Excel MVP "Basenji" wrote in message ... "T. Valko" wrote: Try this... Data in the range Sheet1A2:B9 Array entered** on Sheet2 in cell A2: =INDEX(Sheet1!A$2:A$9,MATCH(LARGE(Sheet1!$B$2:$B$9-ROW(Sheet1!B$2:B$9)/10^10,ROWS(A$2:A2)),Sheet1!$B$2:$B$9-ROW(Sheet1!B$2:B$9)/10^10,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy across to B2 then down to A9:B9. Accounts for possible ties. -- Biff Microsoft Excel MVP "Basenji" wrote in message ... On sheet 1 in column A is the name of the account; same sheet column B is a percentage. Account Percentage Mercy 80.4% Henry 89.6% Fred 0.00% Chris 92.70% Connie 92.00% Alex 92.60% Larry 89.80% Ollie 0.00% On a second sheet (a summary sheet) a formula is needed to sort and rank the accounts so that the account with the highest percentage is at the top of the list, like this, Chris 92.70% Alex 92.60% Connie 92.00% etc I have tried a combination of formulas but have been unsuccessful. Thank you. Okay, now a variation is needed: same accounts but this time the column of numbers (general numbers and not percentages this time) and accounts need to be ranked and sorted with the low number at the top and any accounts that have zero will be at the bottom of the list. Thus it would look like this. Account Minutes Mercy 42 Henry 45 Fred 53 Chris 59 Connie 65 Alex 0 Larry 0 Ollie 0 In the formula you kindly provided I have changed the LARGE function to SMALL, which puts the numbers in order with the smallest one at the top, which is zero. But the zero value in this case is not good and should be at the bottom. The first number can be anything greater than zero. Would an IF function help solve this? Thank you. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank and Sort
In regards to question 3, I am not doing something right to change the
references and the amounts. In column C this is the formula to return the name: =INDEX('Quality Measure Rankings'!A$64:A$74,MATCH(LARGE('Quality Measure Rankings'!$C$64:$C$74-ROW('Quality Measure Rankings'!C$64:C$74)/10^10,ROWS(A$64:A64)),'Quality Measure Rankings'!$C$64:$C$74-ROW('Quality Measure Rankings'!C$64:C$74)/10^10,0)) In column D this is the formula to return the amount (percentage): =INDEX('Quality Measure Rankings'!B$64:B$74,MATCH(LARGE('Quality Measure Rankings'!$C$64:$C$74-ROW('Quality Measure Rankings'!D$64:D$74)/10^10,ROWS(B$64:B64)),'Quality Measure Rankings'!$C$64:$C$74-ROW('Quality Measure Rankings'!D$64:D$74)/10^10,0)) Which of the formulas in column C are referring to the name and which are referring to the amounts? Do some of the references in column C need to be changed to absolute so they do not change when copied to column D with the autofill handle? Thank you for the clarification. "T. Valko" wrote: 1.Does Rows(A$2:A2) refer to sheet 1 or sheet 2 or does it make any difference? Rows(A$2:A2) refers to the first cell the formula is entered in. In this example the first formula was entered in cell A2 hence: Rows(A$2:A2). From a purely technical standpoint, you don't need to include the sheet name in that type of expression. For example, this expression: ROW(Sheet1!B$2:B$9) Can be written as: ROW(B$2:B$9) The sheet name is irrelavent to what the function is doing.. The function is returning the array {2;3;4;5;6;7;8;9}. The function returns the *exact* same array with or without the sheet name: ROW(Sheet1!B$2:B$9) = {2;3;4;5;6;7;8;9} ROW(B$2:B$9) = {2;3;4;5;6;7;8;9} The sheet name is included to reduce confusion and let's the user know that the function is refering to data on the specified sheet. 2.What does 10^10 mean? 10 to the 10th power or 10*10*10*10*10*10*10*10*10*10 or =POWER(10,10) or 10,000,000,000 A typical lookup formula will *always* only find the first instance of the lookup_value and this poses a problem when there are multiple instances of the lookup_value and we need to find all of them. So, we need some method of making every lookup_value unique. Consider this: B2 = 10 B3 = 10 Ok, we have a tie. Here's how we break that tie and make each value unique. B2:B3-ROW(B2:B3)/10^10 cell_value-(row_number/10^10) B2 = 10-(2/10,000,000,000) = 9.9999999998 B3 = 10-(3/10,000,000,000) = 9.9999999997 Now we have all unique numbers to look for! 3.Will this array formula work if the column for the accounts and the percentages are in nonadjacent columns Yes, but then you'd need to use 2 formulas. One for the name and one for the amount. You simply change the the indexed range. With the names in column A and the amounts in column C: For the names: =INDEX(Sheet1!A$2:A$9..... For the amounts: =INDEX(Sheet1!C$2:C$9..... -- Biff Microsoft Excel MVP "Basenji" wrote in message ... Impressive formula. Thank you. Now three questions: 1. Does Rows(A$2:A2) refer to sheet 1 or sheet 2 or does it make any difference? 2. What does 10^10 mean? 3. Will this array formula work if the column for the accounts and the percentages are in nonadjacent columns on sheet 1, such as the name of the account in column A and the percentage in column C? The percentages in column B are for January; the percentages in column C are for February. The same rank and sort is needed for the percentages in column C and subsequent columns so that one can quickly see which account is at the top for each month. Or does the account name have to be adjacent to the percentage for the array formula to function properly? Thank you. "T. Valko" wrote: Try this... Data in the range Sheet1A2:B9 Array entered** on Sheet2 in cell A2: =INDEX(Sheet1!A$2:A$9,MATCH(LARGE(Sheet1!$B$2:$B$9-ROW(Sheet1!B$2:B$9)/10^10,ROWS(A$2:A2)),Sheet1!$B$2:$B$9-ROW(Sheet1!B$2:B$9)/10^10,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy across to B2 then down to A9:B9. Accounts for possible ties. -- Biff Microsoft Excel MVP "Basenji" wrote in message ... On sheet 1 in column A is the name of the account; same sheet column B is a percentage. Account Percentage Mercy 80.4% Henry 89.6% Fred 0.00% Chris 92.70% Connie 92.00% Alex 92.60% Larry 89.80% Ollie 0.00% On a second sheet (a summary sheet) a formula is needed to sort and rank the accounts so that the account with the highest percentage is at the top of the list, like this, Chris 92.70% Alex 92.60% Connie 92.00% etc I have tried a combination of formulas but have been unsuccessful. Thank you. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank and Sort
It looks like you have 3 ranges involved. You're returning data from cols A
& B based on numeric data in col C. Let's do this: 1. Tell me the *exact* location of the names 2. Tell me the *exact* location of the numeric amounts 3. Tell me *exactly* where you want the results to appear -- Biff Microsoft Excel MVP "Basenji" wrote in message ... In regards to question 3, I am not doing something right to change the references and the amounts. In column C this is the formula to return the name: =INDEX('Quality Measure Rankings'!A$64:A$74,MATCH(LARGE('Quality Measure Rankings'!$C$64:$C$74-ROW('Quality Measure Rankings'!C$64:C$74)/10^10,ROWS(A$64:A64)),'Quality Measure Rankings'!$C$64:$C$74-ROW('Quality Measure Rankings'!C$64:C$74)/10^10,0)) In column D this is the formula to return the amount (percentage): =INDEX('Quality Measure Rankings'!B$64:B$74,MATCH(LARGE('Quality Measure Rankings'!$C$64:$C$74-ROW('Quality Measure Rankings'!D$64:D$74)/10^10,ROWS(B$64:B64)),'Quality Measure Rankings'!$C$64:$C$74-ROW('Quality Measure Rankings'!D$64:D$74)/10^10,0)) Which of the formulas in column C are referring to the name and which are referring to the amounts? Do some of the references in column C need to be changed to absolute so they do not change when copied to column D with the autofill handle? Thank you for the clarification. "T. Valko" wrote: 1.Does Rows(A$2:A2) refer to sheet 1 or sheet 2 or does it make any difference? Rows(A$2:A2) refers to the first cell the formula is entered in. In this example the first formula was entered in cell A2 hence: Rows(A$2:A2). From a purely technical standpoint, you don't need to include the sheet name in that type of expression. For example, this expression: ROW(Sheet1!B$2:B$9) Can be written as: ROW(B$2:B$9) The sheet name is irrelavent to what the function is doing.. The function is returning the array {2;3;4;5;6;7;8;9}. The function returns the *exact* same array with or without the sheet name: ROW(Sheet1!B$2:B$9) = {2;3;4;5;6;7;8;9} ROW(B$2:B$9) = {2;3;4;5;6;7;8;9} The sheet name is included to reduce confusion and let's the user know that the function is refering to data on the specified sheet. 2.What does 10^10 mean? 10 to the 10th power or 10*10*10*10*10*10*10*10*10*10 or =POWER(10,10) or 10,000,000,000 A typical lookup formula will *always* only find the first instance of the lookup_value and this poses a problem when there are multiple instances of the lookup_value and we need to find all of them. So, we need some method of making every lookup_value unique. Consider this: B2 = 10 B3 = 10 Ok, we have a tie. Here's how we break that tie and make each value unique. B2:B3-ROW(B2:B3)/10^10 cell_value-(row_number/10^10) B2 = 10-(2/10,000,000,000) = 9.9999999998 B3 = 10-(3/10,000,000,000) = 9.9999999997 Now we have all unique numbers to look for! 3.Will this array formula work if the column for the accounts and the percentages are in nonadjacent columns Yes, but then you'd need to use 2 formulas. One for the name and one for the amount. You simply change the the indexed range. With the names in column A and the amounts in column C: For the names: =INDEX(Sheet1!A$2:A$9..... For the amounts: =INDEX(Sheet1!C$2:C$9..... -- Biff Microsoft Excel MVP "Basenji" wrote in message ... Impressive formula. Thank you. Now three questions: 1. Does Rows(A$2:A2) refer to sheet 1 or sheet 2 or does it make any difference? 2. What does 10^10 mean? 3. Will this array formula work if the column for the accounts and the percentages are in nonadjacent columns on sheet 1, such as the name of the account in column A and the percentage in column C? The percentages in column B are for January; the percentages in column C are for February. The same rank and sort is needed for the percentages in column C and subsequent columns so that one can quickly see which account is at the top for each month. Or does the account name have to be adjacent to the percentage for the array formula to function properly? Thank you. "T. Valko" wrote: Try this... Data in the range Sheet1A2:B9 Array entered** on Sheet2 in cell A2: =INDEX(Sheet1!A$2:A$9,MATCH(LARGE(Sheet1!$B$2:$B$9-ROW(Sheet1!B$2:B$9)/10^10,ROWS(A$2:A2)),Sheet1!$B$2:$B$9-ROW(Sheet1!B$2:B$9)/10^10,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy across to B2 then down to A9:B9. Accounts for possible ties. -- Biff Microsoft Excel MVP "Basenji" wrote in message ... On sheet 1 in column A is the name of the account; same sheet column B is a percentage. Account Percentage Mercy 80.4% Henry 89.6% Fred 0.00% Chris 92.70% Connie 92.00% Alex 92.60% Larry 89.80% Ollie 0.00% On a second sheet (a summary sheet) a formula is needed to sort and rank the accounts so that the account with the highest percentage is at the top of the list, like this, Chris 92.70% Alex 92.60% Connie 92.00% etc I have tried a combination of formulas but have been unsuccessful. Thank you. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank and Sort
Thank you for your input as it solved the problem.
"T. Valko" wrote: Using named ranges... $A$2:$A$9 = Acc (Account) $B$2:$B$9 = Min (Minutes) Array entered in D2: =INDEX(A$2:A$9,MATCH(SMALL(IF(Min0,Min+ROW(Min)/10^10,MAX(Min)+1+ROW(Min)),ROWS(D$2:D2)),IF(Min0, Min+ROW(Min)/10^10,MAX(Min)+1+ROW(Min)),0)) Copied across to E2 then down to D9:E9 -- Biff Microsoft Excel MVP "Basenji" wrote in message ... "T. Valko" wrote: Try this... Data in the range Sheet1A2:B9 Array entered** on Sheet2 in cell A2: =INDEX(Sheet1!A$2:A$9,MATCH(LARGE(Sheet1!$B$2:$B$9-ROW(Sheet1!B$2:B$9)/10^10,ROWS(A$2:A2)),Sheet1!$B$2:$B$9-ROW(Sheet1!B$2:B$9)/10^10,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy across to B2 then down to A9:B9. Accounts for possible ties. -- Biff Microsoft Excel MVP "Basenji" wrote in message ... On sheet 1 in column A is the name of the account; same sheet column B is a percentage. Account Percentage Mercy 80.4% Henry 89.6% Fred 0.00% Chris 92.70% Connie 92.00% Alex 92.60% Larry 89.80% Ollie 0.00% On a second sheet (a summary sheet) a formula is needed to sort and rank the accounts so that the account with the highest percentage is at the top of the list, like this, Chris 92.70% Alex 92.60% Connie 92.00% etc I have tried a combination of formulas but have been unsuccessful. Thank you. Okay, now a variation is needed: same accounts but this time the column of numbers (general numbers and not percentages this time) and accounts need to be ranked and sorted with the low number at the top and any accounts that have zero will be at the bottom of the list. Thus it would look like this. Account Minutes Mercy 42 Henry 45 Fred 53 Chris 59 Connie 65 Alex 0 Larry 0 Ollie 0 In the formula you kindly provided I have changed the LARGE function to SMALL, which puts the numbers in order with the smallest one at the top, which is zero. But the zero value in this case is not good and should be at the bottom. The first number can be anything greater than zero. Would an IF function help solve this? Thank you. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank and Sort
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Basenji" wrote in message ... Thank you for your input as it solved the problem. "T. Valko" wrote: Using named ranges... $A$2:$A$9 = Acc (Account) $B$2:$B$9 = Min (Minutes) Array entered in D2: =INDEX(A$2:A$9,MATCH(SMALL(IF(Min0,Min+ROW(Min)/10^10,MAX(Min)+1+ROW(Min)),ROWS(D$2:D2)),IF(Min0, Min+ROW(Min)/10^10,MAX(Min)+1+ROW(Min)),0)) Copied across to E2 then down to D9:E9 -- Biff Microsoft Excel MVP "Basenji" wrote in message ... "T. Valko" wrote: Try this... Data in the range Sheet1A2:B9 Array entered** on Sheet2 in cell A2: =INDEX(Sheet1!A$2:A$9,MATCH(LARGE(Sheet1!$B$2:$B$9-ROW(Sheet1!B$2:B$9)/10^10,ROWS(A$2:A2)),Sheet1!$B$2:$B$9-ROW(Sheet1!B$2:B$9)/10^10,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy across to B2 then down to A9:B9. Accounts for possible ties. -- Biff Microsoft Excel MVP "Basenji" wrote in message ... On sheet 1 in column A is the name of the account; same sheet column B is a percentage. Account Percentage Mercy 80.4% Henry 89.6% Fred 0.00% Chris 92.70% Connie 92.00% Alex 92.60% Larry 89.80% Ollie 0.00% On a second sheet (a summary sheet) a formula is needed to sort and rank the accounts so that the account with the highest percentage is at the top of the list, like this, Chris 92.70% Alex 92.60% Connie 92.00% etc I have tried a combination of formulas but have been unsuccessful. Thank you. Okay, now a variation is needed: same accounts but this time the column of numbers (general numbers and not percentages this time) and accounts need to be ranked and sorted with the low number at the top and any accounts that have zero will be at the bottom of the list. Thus it would look like this. Account Minutes Mercy 42 Henry 45 Fred 53 Chris 59 Connie 65 Alex 0 Larry 0 Ollie 0 In the formula you kindly provided I have changed the LARGE function to SMALL, which puts the numbers in order with the smallest one at the top, which is zero. But the zero value in this case is not good and should be at the bottom. The first number can be anything greater than zero. Would an IF function help solve this? Thank you. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank and Sort
On Jul 17, 4:24*pm, Luke M wrote:
On sheet1, create a helper column (I'll assume column C), with this formula entered in row 2 and copied down: =RANK(B2,$B$2:$B$6)+IF(COUNTIF($C$1:C1,RANK(B2,$B$ 2:$B$6))0,1,0) On sheet2, A2 formula is: =INDEX(Sheet1!A$2:A$100,MATCH(ROW(Sheet1!$A1),Shee t1!$C$2:$C$100,0)) Copy down as needed, and across 1 column. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Basenji" wrote: On sheet 1 in column A is the name of the account; same sheet column B is a percentage. Account * * Percentage Mercy * * *80.4% Henry * * *89.6% Fred * * * 0.00% Chris * * *92.70% Connie * * 92.00% Alex * * * 92.60% Larry * * *89.80% Ollie * * *0.00% On a second sheet (a summary sheet) a formula is needed to sort andrankthe accounts so that the account with the highest percentage is at the top of the list, like this, Chris * * * * 92.70% Alex * * * * *92.60% Connie * * * 92.00% etc I have tried a combination of formulas but have been unsuccessful. Thank you. This works well when there are at most 2 equal values in the range - what if there are more than 3 or four? |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank and Sort
Try this...
Data in the range Sheet1A2:B9 Enter this array formula** on Sheet2 A2: =INDEX(Sheet1!A$2:A$9,MATCH(LARGE(Sheet1!B$2:B$9-ROW(Sheet1!B$2:B$9)/10^10,ROWS(A$2:A2)),Sheet1!B$2:B$9-ROW(Sheet1!B$2:B$9)/10^10,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Enter this formula on Sheet2 B2: =SUMIF(Sheet1!A$2:A$9,A2,Sheet1!B$2:B$9) Format as Percentage Select both A2 and B2 and copy down to A9:B9. -- Biff Microsoft Excel MVP "Commish" wrote in message ... On Jul 17, 4:24 pm, Luke M wrote: On sheet1, create a helper column (I'll assume column C), with this formula entered in row 2 and copied down: =RANK(B2,$B$2:$B$6)+IF(COUNTIF($C$1:C1,RANK(B2,$B$ 2:$B$6))0,1,0) On sheet2, A2 formula is: =INDEX(Sheet1!A$2:A$100,MATCH(ROW(Sheet1!$A1),Shee t1!$C$2:$C$100,0)) Copy down as needed, and across 1 column. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Basenji" wrote: On sheet 1 in column A is the name of the account; same sheet column B is a percentage. Account Percentage Mercy 80.4% Henry 89.6% Fred 0.00% Chris 92.70% Connie 92.00% Alex 92.60% Larry 89.80% Ollie 0.00% On a second sheet (a summary sheet) a formula is needed to sort andrankthe accounts so that the account with the highest percentage is at the top of the list, like this, Chris 92.70% Alex 92.60% Connie 92.00% etc I have tried a combination of formulas but have been unsuccessful. Thank you. This works well when there are at most 2 equal values in the range - what if there are more than 3 or four? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rank and Sort | Excel Worksheet Functions | |||
Rank then sort then pastelink | Excel Worksheet Functions | |||
rank the numbers / range of data using 'RANK' and 'ABS' | Excel Worksheet Functions | |||
Microsoft Excel Rank and Sort | Excel Discussion (Misc queries) | |||
Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) |