Friday, October 6, 2017

yogi_Using ArrayFormula To Convert String Values To Numbers

Google Spreadsheet   Post  #2259

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Oct-06-2017
Question by Thomas Wu
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/PwDTWGmyZXU;context-place=forum/docs
Using ArrayFormula with Filter to convert string values to numbers
Imagine I have a question bank for students and I'm trying to give them subjective scores on their responses like:
Tried (1 point) , Close (5 points), Correct (10 points).

Then after giving students subjective scores on their test questions, I can later modify the the points awarded to Tried, Close, and Correct in a small table without having to go question by question to change the score.

I'm trying to use a filter to narrow down the questions for each student, then an index/match to look up the value awarded for each question. I don't think I'm using Sum/ArrayFormula the right way though.

You can see my dummy data here: 

The answers I want to receive are in column B and my formula attempt is in column C. 

Thanks for your help in advance!