choice and priority excel column

[murugappan]

I know this can be done in excel. Just new to this whole thing. Here is what I want.

Example - We are having the following 6 days available for a meeting to host at home so a member can pick and choose.
Now we have 6 members who we will ask their first choice and second choice of dates and may be third so they host this meeting at their home.

Meeting dates to choose from - Nov17, Nov23, Nov24, Nov30, Dec1, Dec7

Person A - Nov17, Dec 7, Dec 1
Person B - Nov 17, Nov 30, Dec 7
Person C - Dec 7, Nov 24, Nov 23
Person D - Dec 1, Nov 30, Nov 23
Person E - Nov 23, Nov 24, Dec 1
Person F - Nov 24, Dec 1, Dec 7

Is there a excel function which probably calculates some weightage etc and assign the best date based on the choice.

For 6 people it is easier to do via mutual talk, but we have 15 days and 15 members where we want to equally distribute.

Any help or formula will be helpful or a different logic if experts suggest it will be helpful.

Muru

[José Augusto]

Hi

See if this helps you

First criterion: assign weights to preferences;
Second criterion: tie by the most frequent date (lowest weight).

Organize your data in a table with Name, date 1st.pref., date 2nd.pref, date 3th.pref)
See the file with an implementation of that where C2:E2 are preferences weights and B3 weight for the most frequent date.

[murugappan]

Thanks for the posting. I am not following the logic in my head and not sure how to derive the answer from the weights.
I looked at the excel and amazing work from your end. Thanks for the response and the time taken to post something meaningful.

[José Augusto]

You are welcome.

I suppose that in weighing the problem one must begin by weighing the relative importance of the choice, that is, how much more balanced that choice is.

See the following example: Suppose that five have first preference as day 9 and second as preference there are six that indicate day 16.

What will your decision be? If you think it would be preferable to set day 9 then you are assuming that 5w₁>6w₂ ie w₁>(6/5) w₂. Note:w_i are weights.
If you think now you would only choose the 16th if there were 8 people and choosing that day as a second preference is assuming that 5w₁<8w₂, ie w₁<(8/5)w₂.

For simplicity, if you set the value w₂ = 5 you would indicate that w₁ would be a value between 6 and 8 ie w₁=7.

The reasoning can be used to construct the weight w3 relative to w2 or assume the same ratio.

Thus, if w₃=5 then w₂=7 and therefore (8x7/5)>w₁>(6x7/5) ie w₁=(7x7/5) ~ w₁=10.

I hope I made it clear.