question

SarahHotz-3732 avatar image
0 Votes"
SarahHotz-3732 asked emizhang-msft answered

How to score a multi-answer question in an Excel Formula

I'm trying to score the following question in Excel:

Tick all that are true for you. I would travel …
interprovincially, within South Africa, for business or necessity
interprovincially, within South Africa, for pleasure
internationally for business or necessity
internationally for pleasure.

If the respondent ticks none of the options, they get a score of 4. If they score any one of the options, they get a score of 3. If they score any two of the options, they get a score of 2. And so on down to a score of 0 if they've ticked all the options.

I have this current information in a single cell per respondent in my Excel sheet.
So if they didn't tick any options, the cell is empty. If they ticked the first two options, the cell contains "interprovincially, within South Africa, for business or necessity, interprovincially, within South Africa, for pleasure"

How can I set up a formula to score this information, as above? I just want a value of 0, 1, 2, 3 or 4.

office-excel-itpro
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SarahHotz-3732,
How did you Tick the options? Did you use specific symbols or other methods?
I suggest you try to provide a sample table and the result you need here, so that I can get more accurate solutions to this problem. I’m glad to help and follow up your reply.

0 Votes 0 ·
SarahHotz-3732 avatar image
0 Votes"
SarahHotz-3732 answered SarahHotz-3732 published

The original question was in a Google Form and I've exported it into Excel for the analysis.

I'm not sure how to input a table into this field to post a sample table!

This is all one column in the table, with each line representing a different row in the table. The first line is the column heading. As for the result I need, I need a numerical score of 0, 1, 2, 3 or 4 in the next column in the table.

Tick all that are true for you. I would travel …
interprovincially, within South Africa, for business or necessity, interprovincially, within South Africa, for pleasure
interprovincially, within South Africa, for business or necessity, internationally for pleasure
interprovincially, within South Africa, for business or necessity, internationally for business or necessity
internationally for business or necessity
interprovincially, within South Africa, for business or necessity, interprovincially, within South Africa, for pleasure
interprovincially, within South Africa, for business or necessity, interprovincially, within South Africa, for pleasure, internationally for business or necessity, internationally for pleasure
internationally for business or necessity
interprovincially, within South Africa, for business or necessity, interprovincially, within South Africa, for pleasure, internationally for business or necessity, internationally for pleasure
internationally for business or necessity
interprovincially, within South Africa, for business or necessity, interprovincially, within South Africa, for pleasure, internationally for business or necessity, internationally for pleasure

Not sure if that helps?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

emizhang-msft avatar image
0 Votes"
emizhang-msft answered

Try this formula:

 =IFERROR(IF(FIND("interprovincially, within South Africa, for business or necessity",A1)>0,1,0),0)+IFERROR(IF(FIND("interprovincially, within South Africa, for pleasure",A1)>0,1,0),0)+IFERROR(IF(FIND("internationally for business or necessity",A1)>0,1,0),0)+IFERROR(IF(FIND("internationally for pleasure",A1)>0,1,0),0)

136912-497.png



497.png (35.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.