Excel Question: How to Know Ranking Position of Items in a List?

Hi, I'm trying to know how to rank items depending on their position.

Example:

I have 3 persons:
Alice, Bob, and Charlie

I have a list of 15 items to rank:

- Apple
- Avocado
- Bar of chocolate
- Cinnamon roll
- Deli sandwhich
- Eggo Waffles
- Fried chicken
- Fruit cocktail
- Grapes
- Hash brown potatoes
- Hamburger
- Honey
- Hot dog
- Hot sauce
- Hummus

Each person was asked to rank their preferred item, from top to bottom. The top being the one they like the most, and at the bottom the one they like the least.

So, Alice ranked her list like this:

- Fried chicken
- Hummus
- Eggo Waffles
- Hamburger
- Hot dog
- Bar of chocolate
- Cinnamon roll
- Deli sandwich
- Hot sauce
- Hash brown potatoes
- Apple
- Avocado
- Grapes
- Honey
- Fruit cocktail

Bob ranked list is:

- Grapes
- Fried chicken
- Hummus
- Avocado
- Apple
- Honey
- Bar of chocolate
- Cinnamon roll
- Deli sandwich
- Eggo Waffles
- Hash brown potatoes
- Hamburger
- Hot dog
- Hot sauce
- Fruit cocktail

And Charlie's list was ranked as the following:

- Fried chicken
- Hummus
- Honey
- Eggo Waffles
- Cinnamon roll
- Hamburger
- Avocado
- Bar of chocolate
- Hash brown potatoes
- Deli sandwich
- Hot dog
- Grapes
- Hot sauce
- Apple
- Fruit cocktail

In this example, Fried Chicken was clearly preferred,

  • ranking 1st for Alice
  • ranked 2nd for Bob
  • ranked 1st for Charlie

Question:
How can I know, for each item, how preferred it was?

Output

Example of desired output:

- Fried Chicken: 1st position
- Hummus: 2nd position
- Fruit cocktail: 15th position
- etc.

Ideas

Thought about adding a number next to each item in each list. 15 points to the one at the top, 1 point to the one at the bottom of the list.

So,
Alice's list would look like this after I add points next to it:

- Fried chicken (15 points)
- Hummus  (14 points)
- Eggo Waffles  (13 points)
- Hamburger  (12 points)
- Hot dog  (11 points)
- Bar of chocolate  (10 points)
- Cinnamon roll  (9 points)
- Deli sandwich  (8 points)
- Hot sauce  (7 points)
- Hash brown potatoes  (6 points)
- Apple  (5 points)
- Avocado  (4 points)
- Grapes  (3 points)
- Honey  (2 points)
- Fruit cocktail  (1 point)

Output v2

Another example of desired output:

- Fried Chicken: 1st position, 44 points (15+14+15)
- Hummus: 2nd position, 41 points (14+13+14)
- Fruit cocktail: 15th position, 3 points (1+1+1)
- etc

But since there are a lot of persons to check, thought about how could I automate this with KM and Excel.

Searched StackOverflow and Google but had no luck.

Thanks!

I remember doing this kind of thing (many) years ago. So I knew that using Google to search for “ranked voting excel” might bring useful results.

One interesting result is at Ranked voting - Wikipedia which introduces Borda counting amongst other things (your v2 I believe)

Another one is Voting Excel Template and Add-In (Borda Counting and Schulze Method)

These might help you out I hope but not with the KM side…

1 Like

Good response, and polite. This is an algorithm question, not a KM question, although KM could implement any solution once he decides which algorithm he wants to implement.

It's like asking "which sort algorithm is best"? That's not a KM question either.

Thanks @tiffle
Learned something new :slight_smile:

1 Like

Found a Reddit thread with a good example:

https://archive.md/J6Cn2

1 Like