I'm creating a survey that a number of users will participate in. They will have to place 20 fruits in a Top 10 ranking format. Each user will only be able to rank 10 fruits, so there will be 10 fruits that don't get a rank.
How can I collate this information to output an overall Top 10 as ranked by all users in the survey? A formula or even piece of JS/Python code to loop over the rows would be great
Below is an example of the table/spreadsheet that I am going to receive.
I initially thought that summing all the rankings and then ordering by lowest total first would give the standings in a correct order*, but using that method would mean that Kiwi would come out on top even though it received none rankings. Also Mango would come before Banana even though Banana recieved a first place ranking.
| Participant A | Participant B | Participant C | Participant D | Participant E | SUM | |
|---|---|---|---|---|---|---|
| Apple | 8 | 4 | 8 | 8 | 28 | |
| Banana | 5 | 1 | 6 | |||
| Blackberry | 6 | 6 | 12 | |||
| Blueberry | 4 | 5 | 7 | 2 | 18 | |
| Cherry | 8 | 10 | 18 | |||
| Fig | 3 | 3 | 10 | 16 | ||
| Grape | 1 | 9 | 7 | 9 | 26 | |
| Grapefruit | 2 | 4 | 4 | 10 | ||
| Kiwi | 0 | |||||
| Lychee | 3 | 3 | ||||
| Mango | 5 | 5 | ||||
| Nectarine | 6 | 1 | 9 | 16 | ||
| Orange | 10 | 3 | 13 | |||
| Papaya | 7 | 2 | 10 | 19 | ||
| Peach | 7 | 3 | 7 | 17 | ||
| Pineapple | 1 | 8 | 9 | |||
| Pomegranate | 2 | 6 | 6 | 14 | ||
| Raspberry | 9 | 9 | 4 | 2 | 24 | |
| Strawberry | 5 | 5 | 10 | |||
| Watermelon | 10 | 1 | 11 |
*I imagine there are many ways to do this, so might not be a single correct way
I have solved this by creating a scoring system dependant on the rank of each fruit, and have expanded this to also add a weighted score to higher ranked fruit. I'm going to write this for JavaScript because that is how I tested it, but it can also be easily done in Google Sheets.
To start, create a base point array that will convert the rank into a corresponding point which will be awarded to the item. This can either be restricted to only award the top 𝑥 amount of ranked items with points, or the total number of items to be ranked. Then reverse the rank so that 1st obtains the highest score and last obtains the lowest score.
This can then be attributed to each set of rankings to give each item a score that can be summed. Taking into account items that haven't been ranked receiving a score of 0.
But this can create a skewed list where items that are ranked at a low position many times can easily over take items ranked at a high position only a few times.
To add weighting to the points simply use an exponential formula that will then inflate the higher ranked points more than the lower ranked points. This can be achieved by using either the base point value to the power of a coefficient, or the coefficient to the power of the base point value. In my opinion the latter produces more of a curve, but the coefficient must be tested otherwise the gap between the largest and smallest point scores can be too big.
I will try and create a Google Sheets with the formula included, but for now the JavaScript code is below.
Hopefully someone else finds this helpful!
const items = ['Apple', 'Banana', 'Blackberry', 'Blueberry', 'Cherry', 'Fig', 'Grape', 'Grapefruit', 'Kiwi', 'Lychee', 'Mango', 'Nectarine', 'Orange', 'Papaya', 'Peach', 'Pineapple', 'Pomegranate', 'Raspberry', 'Strawberry', 'Watermelon'];
const results = [
['Grape', 'Pomegranate', 'Fig', 'Blueberry', 'Banana', 'Blackberry', 'Papaya', 'Apple', 'Raspberry', 'Watermelon'],
['Pineapple', 'Grapefruit', 'Fig', 'Apple', 'Blueberry', 'Nectarine', 'Peach', 'Cherry', 'Raspberry', 'Orange'],
['Nectarine', 'Papaya', 'Lychee', 'Raspberry', 'Mango', 'Pomegranate', 'Blueberry', 'Pineapple', 'Grape', 'Fig'],
['Banana', 'Blueberry', 'Peach', 'Grapefruit', 'Strawberry', 'Pomegranate', 'Grape', 'Apple', 'Nectarine', 'Cherry'],
['Watermelon', 'Raspberry', 'Orange', 'Grapefruit', 'Strawberry', 'Blackberry', 'Peach', 'Apple', 'Grape', 'Papaya']
];
const top_x = 10 /* items.length */;
const exponential_coefficient = 1.3;
const sortObject = (object) =>
Object.fromEntries(Object.entries(object).sort(([, a], [, b]) => a - b).reverse());
const base_points = new Array(top_x).fill(null).map((value, index) => Math.abs(index - top_x));
const exponential_points = base_points.map((points) => Math.round(exponential_coefficient ** points));
const base_scores = items.reduce((acc, curr) => ((acc[curr] = 0), acc), {});
const exponential_scores = items.reduce((acc, curr) => ((acc[curr] = 0), acc), {});
results.forEach((set) =>
set.forEach((item, index) => {
base_scores[item] += base_points[index] || 0;
exponential_scores[item] += exponential_points[index] || 0;
})
);
console.log({ top_x, base_points, exponential_points, base_scores: sortObject(base_scores), exponential_scores: sortObject(exponential_scores) });