How to get an average of multiple symbols (harvey balls) on a single row?

Matt K

Hi,

I have a form that once completed populates a sheet. The form has about 100 items. The idea is that 100 items represent 5 categories. For each individual that completes the form I need to provide an average from the 100 responses that correlate to each category. I have the columns next to each so I can use the "range" option. Then I need an average of all 5 categories values.

I am able to do basic COUNT formulas, but anything that goes beyond a single value for the Harvey Ball response I have not been able to come up with the right formula.

Matt K

To clarify, the "values" I mentioned need to be numerical, not with the symbol.

Paul Newcome

Can you provide a screenshot of your sheet with sensitive/confidential data removed, blocked, and/or replaced with "dummy data" as needed?

Matt K

Thank you, Paul.

Screenshot provided. Bill completed a survey, Topic 2 consists of three questions (Topics 2-1 through 2-3). Bill doesn't know they are grouped and doesn't care. Management wants to know what is Bill's "score" Topic 2. Assuming completely filled harvey ball has a value of "1" , three quarter is 0.75, half is "0.50", quarter is "0.25" and empty is "0" (i'd like to assign numbers 1,2,3,4 instead, but this is not as important). The average for Topic 2 for Bill is 0.33.

Is it more clear now?

Gil Nash

Paul may have a better answer without helper columns but I was able to get these counts using helpers

=IF([2-1]@row = "empty", 0, IF([2-1]@row = "quarter", 0.25, IF([2-1]@row = "half", 0.5, IF([2-1]@row = "three quarter", 0.75, 1))))

Then just =AVG([2-1 (Number)]@row:[2-3 (Number)]@row) for highlighted cell

Paul Newcome

Based on the screenshot...

Using @Gil Nash's solution could end up requiring 9 helper columns in total. One for each symbol column to convert it to a numeric value.

You could get away from the helper columns by dropping each nested IF directly into the AVG function, but that could get rather unwieldy pretty quickly.

=AVG(IF([2-1]@row = "empty", 0, IF([2-1]@row = "quarter", 0.25, IF([2-1]@row = "half", 0.5, IF([2-1]@row = "three quarter", 0.75, 1)))), IF([2-2]@row = "empty", 0, IF([2-2]@row = "quarter", 0.25, IF([2-2]@row = "half", 0.5, IF([2-2]@row = "three quarter", 0.75, 1)))), IF([2-3]@row = "empty", 0, IF([2-3]@row = "quarter", 0.25, IF([2-3]@row = "half", 0.5, IF([2-3]@row = "three quarter", 0.75, 1)))))

If you have to change anything, that many column references and parenthesis tucked in and whatnot would be extremely tedious, but it is an option.

I personally would go somewhere in the middle with two helper columns and a few INDEX/MATCH formulas nested into the AVG.

If you set up a table like the screenshot below, you can reference that table and keep a much shorter overall formula. Edits would also be much easier because you would only need to adjust the table instead of the formula itself (unless you are adding or removing an entire column from the AVG).

To pull the first number, we use:

=INDEX([Number Table]:[Number Table], MATCH([2-1]@row, [Symbol Table]:[Symbol Table], 0))

We do that again for each of the other columns and drop them into the AVG function to end up with:

=AVG(INDEX([Number Table]:[Number Table], MATCH([2-1]@row, [Symbol Table]:[Symbol Table], 0)), INDEX([Number Table]:[Number Table], MATCH([2-2]@row, [Symbol Table]:[Symbol Table], 0)), INDEX([Number Table]:[Number Table], MATCH([2-3]@row, [Symbol Table]:[Symbol Table], 0)))

Matt K

Thanks, Gil and Paul. I will try the suggestions. This is very helpful. This should give me enough to get closer to the results I'm looking for. I'll post the outcome when I can.

Matt K

@Paul Newcome Problem with this one:

=AVG(IF([2-1]@row = "empty", 0, IF([2-1]@row = "quarter", 0.25, IF([2-1]@row = "half", 0.5, IF([2-1]@row = "three quarter", 0.75, 1)))), IF([2-2]@row = "empty", 0, IF([2-2]@row = "quarter", 0.25, IF([2-2]@row = "half", 0.5, IF([2-2]@row = "three quarter", 0.75, 1)))), IF([2-3]@row = "empty", 0, IF([2-3]@row = "quarter", 0.25, IF([2-3]@row = "half", 0.5, IF([2-3]@row = "three quarter", 0.75, 1)))))

is that it does not scale. If a topic is 25 columns, it's not realistic to create a formula like the below for every column I want to reference. I'm surprised the "range" for columns option in the formula will not help me with this type of statement.

Almost sounds like the easiest option is manually go back to all 100 items, change their responses to numerical values and then use the average.

Paul Newcome

That is correct. The Nested IF solution doesn't scale. That is why I provided an alternative using a table and INDEX/MATCH.

And yes. The absolute EASIEST way would be to change the responses themselves to numerical values so that there is nothing that needs converted before averaging.