I've built a form that dumps data to a Google spreadsheet and that data, studio reads it... and turns that into a dashboard.. only problem is the text doesn't convert very well for building a dash board. I need Numbers!

That being said I would like to turn my yes or no responses, which could be in any cell and be either or into a 0 or a 1.

form response date 4-22-21, cell A1 has a YES or a NO response.
I would like this response to be shown as a "1" for yes and "0" if its no in another cell like A7.

Any help would be appreciated.

  • Welcome. Please remember that as per site guidelines when an answer addresses your question, you should accept it and even upvote it so others can benefit as well. Commented Apr 23, 2021 at 1:36

1 Answer 1


Create a new sheet/tab and try the following formula. (You should NEVER touch the Form responses tab)

=INDEX(SWITCH('Form responses'!B2:B,"YES",1,"no",0,""))

enter image description here

(please -as always- adjust formula according to your ranges and locale)

Functions used:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.