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

Sarah Hotz 1 Reputation point
2021-09-13T09:33:59.967+00:00

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.

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,649 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Sarah Hotz 1 Reputation point
    2021-09-14T07:21:01.297+00:00

    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?

    0 comments
  2. Emi Zhang-MSFT 22,086 Reputation points Microsoft Vendor
    2021-10-01T07:30:08.773+00:00

    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

    0 comments