Rank a list of proposals

[Gemini0531]

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.

 

[Bernie Deitrick]

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]

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]

That happens if you have blanks.

=IF(COUNTBLANK(B2:U2)>0,"Not filled in yet",IF(SUM(1/COUNTIF(B2:U2,B2:U2))<>20,"Number missing or
repeated","All numbers 1-20 used"))

array entered.

HTH,
Bernie
MS Excel MVP