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: 

https://docs.google.com/spreadsheets/d/1oqs19LRnrXhj9Of_-TYsNhXwkbmPKqpTQDUeynnWUHY/edit?usp=sharing

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

Thanks for your help in advance!

yogi_Repeat Sheet Names By Number Of Items In Respective Sheet Names In One Combined List

Google Spreadsheet   Post  #2248

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Sep-25-2017

question by: Thomas Wu

https://productforums.google.com/forum/#!topic/docs/uL6otiR9PNs;context-place=forum/docs
Repeating value X times depending on number of entries in a range
I currently use a Filter formula to grab all the relevant entries on multiple tabs:

={filter(Fruits!A:A,Fruits!A:A<>"",row(Fruits!A:A)>1);filter(Vegetables!A:A,Vegetables!A:A<>"",row(Vegetables!A:A)>1)}

That formula above will output 1 list of items from the "Fruits" tab and the "Vegetables" tab.

It's particularly helpful because I don't know how long the list from both tabs will be as they will grow/shrink daily.

What I want to do next is to add a column that describes which worksheet each entry comes from.

I know I can do this via a formula that will check each worksheet to find the name:

If(match(key,Fruits),"Fruits","Vegetables")

But I imagine there is another way to count the number of filter entries in the first formula and repeat "Fruits" for as many entries in the first part of the formula and then vegetables for the following entries. Any ideas of how to do this? Here's a spreadsheet with dummy data:

https://docs.google.com/spreadsheets/d/1d9asz3Wu9y0Eby-9w0DQkr3M-E9DRcvsVOnJWRaT_4M/edit#gid=0

Cell A3 is where I'd like to have the formula entered. Thanks in advance if anyone has any ideas!

-Thomas