Averaging A Multi-Select Dropdown for a Value

DRogers

Hello, I would appreciate some help with a formula. I'm still new to Smartsheet and don't understand how to make use of some advanced functions yet.

I have two sheets that I am working across;

The first has detailed information on items, but the only two pieces relevant for this are a score given to each item, and the categories the item may belong to set as a multiple choice dropdown, think;

The second sheet combines the information from the first sheet into more useful forms. The important columns for what I'm having trouble with are a single choice column and the average column I'm working on, think;

I'm trying to have the Score Average column (sheet 2) return an average from all rows in the Score Column (sheet 1) for which the choice made in the Single Choice Column (sheet 2) matches any choice made in the Multiple Choice Column (sheet 1).

I started with AVERAGEIF, but that only returned exact matches between the choice columns, not any occurrence of the single choice across the multiple choice columns. I've tried writing a formula with INDEX, MATCH, and CONTAIN, but I don't understand how to work with them well enough to get a result.

I was able to find some similar posts from the past, but between them not being quite close enough, and my lack of knowledge, I just kept getting unparseable errors when trying to make them fit.

Thank you in advance for any help you can offer.

Leibel S

@DRogers

Something like this should work:

=AVERAGEIF({Multiple Choice Column}, HAS(@cell, [Single Choice Column]@row), {Score column})

Rename the range/column names (bolded) as needed

Leibel S

@DRogers

Something like this should work:

=AVERAGEIF({Multiple Choice Column}, HAS(@cell, [Single Choice Column]@row), {Score column})

Rename the range/column names (bolded) as needed

DRogers

This did work, but could I ask a followup?

What is the @cell doing in the HAS function?

I'm reading this as the HAS is searching the cell the formula is written in for the value in the single choice column on the same row, but what is that doing (or why does that work)? Why isn't the HAS function being pointed to the multiple choice column to search through it for the value in the single choice column on the same row? I think this issue is why I couldn't get this to work myself.

Thank you for helping me with the initial question, and thank you again if you are willing to offer further help.

Leibel S

You can use @cell to reference the value withing the range that the function is checking against.

So in this case the formula loops through the {Multiple Choice Column} and we are checking @cell (the current value it is evaluating) to see if it has the value in [Single Choice Column]@row