Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rank a list of proposals
I have a list of 20 or so proposals that requires 55 voting member to rank in
priority of importance with 1 being most important and 20 being least important. Afterwards, all the rankings are added, and the one with the lowest value has the highest priority. How do I establish a "Rank" column to ensure that no 2 proposals have the same ranking value AND that no proposal get a value greater than the max number of proposal. That is, if there are 20 proposal, one of the proposals does not get assigned a value of 21. Please help. Any assistance is greatly appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rank a list of proposals
You could use a few extra columns and rows to do what you want. I'm assuming that your table is in A1:U56, with proposals listed in row 1, voters n column A, and responses in rows 2 to 56, columns B to U. If your 20 or so is actually greater, simply use more columns, and change the 20 in each formula accordingly. In the first extra column, check that (for each voting member) no values are greater than 20, say, in cell V2 (copied down to match your table) =IF(MAX(B2:U2)20,"This voter has a number that is Too High!","All less than 21") In the next column (W2), you could flag if any numbers are missing - with this array formula (Entered using Ctrl-Shift-Enter) =IF(SUM(1/COUNTIF(B2:U2,B2:U2))<20,"Number missing or repeated","All numbers 1-20 used") Again, copy down to match your table. Then to RANK the proposals, you would first use this formula in cells B57 =SUM(B2:B56) Then to RANK them, =RANK(B57, $B$57:$U$57,1) As for tie breaking - well, you don't give enough information to do that. You could find the one with the highest count of 1 values, or 2 values, or 3 values, etc. HTH, Bernie MS Excel MVP "Gemini0531" wrote in message ... I have a list of 20 or so proposals that requires 55 voting member to rank in priority of importance with 1 being most important and 20 being least important. Afterwards, all the rankings are added, and the one with the lowest value has the highest priority. How do I establish a "Rank" column to ensure that no 2 proposals have the same ranking value AND that no proposal get a value greater than the max number of proposal. That is, if there are 20 proposal, one of the proposals does not get assigned a value of 21. Please help. Any assistance is greatly appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rank a list of proposals
Thanks, Bernie.
The first formula worked great, but the second one (IF (SUM (COUNTIF...) keeps returning with a "#DIV0!" error stating I cannot divide by 0 - there are no zero ranking. Please help. "Bernie Deitrick" wrote: You could use a few extra columns and rows to do what you want. I'm assuming that your table is in A1:U56, with proposals listed in row 1, voters n column A, and responses in rows 2 to 56, columns B to U. If your 20 or so is actually greater, simply use more columns, and change the 20 in each formula accordingly. In the first extra column, check that (for each voting member) no values are greater than 20, say, in cell V2 (copied down to match your table) =IF(MAX(B2:U2)20,"This voter has a number that is Too High!","All less than 21") In the next column (W2), you could flag if any numbers are missing - with this array formula (Entered using Ctrl-Shift-Enter) =IF(SUM(1/COUNTIF(B2:U2,B2:U2))<20,"Number missing or repeated","All numbers 1-20 used") Again, copy down to match your table. Then to RANK the proposals, you would first use this formula in cells B57 =SUM(B2:B56) Then to RANK them, =RANK(B57, $B$57:$U$57,1) As for tie breaking - well, you don't give enough information to do that. You could find the one with the highest count of 1 values, or 2 values, or 3 values, etc. HTH, Bernie MS Excel MVP "Gemini0531" wrote in message ... I have a list of 20 or so proposals that requires 55 voting member to rank in priority of importance with 1 being most important and 20 being least important. Afterwards, all the rankings are added, and the one with the lowest value has the highest priority. How do I establish a "Rank" column to ensure that no 2 proposals have the same ranking value AND that no proposal get a value greater than the max number of proposal. That is, if there are 20 proposal, one of the proposals does not get assigned a value of 21. Please help. Any assistance is greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
generating rank-ordered list | Excel Discussion (Misc queries) | |||
One list unsorted, but two groups - I need to rank in each group | Excel Discussion (Misc queries) | |||
RANK, duplicate ranking but no gaps in rank | Excel Worksheet Functions | |||
Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) | |||
how can I rank within a filtered list in Excel? | Excel Discussion (Misc queries) |