Formula for percent match to a multi select column

Hi, I'm working matching mentors and mentees. We would like to match them based on attributes they both selected in a multi select column. As in, choose your top three competencies. Now I would like to make a formula to determine the percent match of a mentee to a mentor's selection. That is Mentee 1 selected A, B, and C. Mentor 1 selected A, D, E; mentor 2 selected A, B, E; Mentor 3 selected A, B, C. So mentor 1 should be 33%, mentor 2 should be 66%, and mentor 3 should be 100%. What is the best way to do this with the new multi select drop down columns?

Thanks,

Liz

Find more posts tagged with

Formulas

Comments

Paul Newcome

It would honestly be much easier if you were to have either the mentors or the mentees use 3 single select columns as opposed to both using multi-select. Is that an option?

eceisin

The applications are complete. I could break out into 3 columns after the fact. Is there a good way to break out the three selections then?

Paul Newcome

Ok. Will it ALWAYS be 3 selections that are made, or is there the possibility someone will only choose one or two?

eceisin

There are times where users selected one or two.

Paul Newcome

Ok. And which way will you be making the comparison? The mentor is a ##% match to the mentee or the mentee is a ##% match to the mentor?

EDIT FOR CLARIFICATION:

Basically I am asking...

If the Mentee only selects 1 option, but the Mentor has selected 3...

The Mentee would only be a 33% match to the mentor, but the mentor would be a 100% match to the Mentee.

It could also be asked the other way around... If the Mentor only selected one, but the Mentee selected three.

Where do you want your priority?

eceisin

Mentor match to the mentee

Paul Newcome

Ok. HERE is a link to a published sheet. In this sheet you will see where I parsed out the Mentee selections. I then generated a number based on how many matches between mentor and mentee there were and divided that into how many selections the Mentee made. This gave me a percentage of how good a fit the mentor was for the mentee based on what the mentee felt was important.

Please let me know if you need further clarification or help modifying it to fit your exact needs after reviewing.

eceisin

THIS IS AMAZING!!! Thank you so much!

Pam Ferguson

@Paul Newcome Do you have some time to connect on this? We have a need but I am wondering about the scale of your solution and want to know more.

Sign in to join the conversation!

Quick Links

Trending discussions

How do I get a column that shows the automated % complete for each activity?
I am struggling to get any formulas to work, I need a column that shows the percentage complete from the start date to the finish date - shouldn't be that hard but this is causing me so much grief. Every formula I find doesn't seem to work and I am not savvy enough to know how to update the formula so that it can work.…
Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?