Scoring Inventories for SPED?

We are in the middle of evaluation seasons and that means the use of a lot of tools like “Employability Skills Inventory”. For just one survey-taker the quickest thing is to have them use a pen to circle a thing on paper and then the person who administered the survey adds up a bunch of points (some going 1,2,3 some 3,2,1, etc) in a bunch of categories based on color coding or something,… and finally that is used to help the survey taker.

But if we are doing a bunch of these, is there a good way to automate them? VLookups in Excel? Using Google Survey to get answers into a spreadsheet? Anybody else already solved this? Googling “inventory scoring using spreadsheets” didn’t turn up anything helpful…

If this were my job, I could code each kind of survey into Excel, etc. It’s just rules, but it’s pretty time-consuming. Any ideas about how to use these kinds of tools more efficiently?

I’d be inclined to use a Google survey. Based on what you’ve said, I can’t see anything it couldn’t do.

So the Google Survey is going to ask the taker to choose “Very True”, “Somewhat True”, or “Not True” which will then get spit into a Google sheet as text.

For some questions VT = 3, ST = 2, and NT =1 but for others VT = 1, ST = 2, and NT = 3. We have to take those responses, turn the text into the proper number and then add up to get scores by category. In Excel, I could program a VLookup to assign the right points for each question, but I don’t know that Google sheets can do that.

We thought of making the Google Survey a self-scoring quiz, but it will either give 3 points or 0 points for answers in each question. You can’t have it give 3 for one choice and 2 or 1 for another choice.

Sheets has excellent under the hood power now. When it first came out, it couldn’t do a lot of the stuff that Excel can, but over the years it’s closed the gap.

You could do this with nested if statements, for one way. Here’s a sample sheet that shows how it would work:
Sample Formatting Conversion Sheet

The formula I used is this:
=IF(C2 = “very true”, 3, IF (C2 = “somewhat true”, 2, IF (C2 = “not true”, 1)))

The syntax is:
IF (condition, result, else check this thing)

The third parameter (else check this thing) is optional. You can also do:
IF (condition, result)

The formula had trouble with any answer that was solely “true” because it’s reserved in Sheets as a Boolean value. That’s an easy thing to work around, though.

So you could collect answers from the survey in one column of the spreadsheet, then have another column elsewhere in the spreadsheet convert them to numbers depending on what you wanted to do with that question.

I can explain more if something’s not clear.

I’ll give that a shot - thanks

1 Like

Sure thing. Let me know if you get stuck somewhere. Happy to help.

We got one set up successfully, so I think the SPED team will be able to copy-paste their way to automation.


1 Like