# How can I get working formulas to convert quiz data into text fields without errors?

I’m trying to work out a simple way to generate some text-based responses from sheets that are derived from some basic field entries that come in from Gravity Forms. I feel it should be quite simple, but Sheets doesn’t want to generate responses based on the data in cells being formula-generated(?!)

For example, imagine a series of questions in Gravity Forms, where each asks the person to answer how they feel on a 10 point scale, and they click a radio button on 1-10. The first 2 answers (for example) would then each be sent to their given 2 cells in Sheets. (Presumably in the same column, descending down 2 rows)

From there, I want to be able to convert the inputted data into a more simplified form in the next column over by breaking the responses in half - Eg, based on 0-5, vs 6-10, if someone answered “3”, it would allocate a value of, say A. If they answered “7” it would give B, (or it could be 1 or 2 instead of letters), to denote “good” versus “bad”. So you’d have a cell that reads, say, A in the first column if someone had answered “3” from the buttons, and if someone answered 7 on the next set, they’d score a B in the next. I’ve figured out how to get it to do this.

From there, we would end up with 4 possible sets of answers: AA, AB, BB, and BA, which I have also achieved by using the 2-step formula of `=if(b2>4,"A","B")` and then to get the combination code I’ve tried to use `=C2&""&C3`. However, when I have then tried to use another `if` function in the next cell to add a specific body of text dependent on which of these resulting AA, AB, BB, BA fields is returned, it gives a false response (seemingly because the source cell is based on a formula ~and/or is letter based~, when I use just a number that is manually added I can get the formula to give me 1 selection of text). We are also wanting to be able to add multiple ‘if’ constraints here so that the cell will end up displaying the appropriate text based on the AA AB etc… answers, so the cell ‘chooses’ the correct text from 4 options.

The idea is that we would repeat this a bunch of times over - perhaps 10-20, to generate a somewhat tailored report based on the simple data provided by the person’s form submission.

Feel free to create new rows (not edit existing ones) to demonstrate a working method.

• Hi and welcome. I get the sense that your question is based on your expectations rather than actual data. For example, you say “Presumably in the same column, descending down 2 rows”. What do you mean by “presumably”? Would you please edit your question to include some actual (or dummy) data that reflects EXACTLY how the data is delivered from Gravity forms. Would you please also show what you have tried to resolve this problem. Sep 30, 2021 at 23:50
• Hi Tedinoz: I just meant "for argument's sake" - as I'd presume the incoming data would be in say column 1 or 2, and run across in rows. Link to a test page with formulas here: docs.google.com/spreadsheets/d/… - the Gravity forms data is very simple; it would just be a simple number in each initial cell. Oct 1, 2021 at 6:02
• You said it again: I'd presume... You can't presume, you need to know. Have you read Create rows in Google Sheets for new Gravity Forms submissions? You presume each response will be in a column with each row containing the answer to a question. But this means that subsequent responses will be in adjacent columns so you can't have formula in Columns C, D & E (or any other column). Oct 1, 2021 at 10:26
• I suggest each response will populate a row with answers to question in a given columns (refer my suggested layout on Sheet="Sheet1_ejblayout"). This is more than a difference of opinion-the structure of the formula depends on the data layout. Oct 1, 2021 at 10:26

You want to convert answers certain questions to meaningful text but your existing formula is not working.

There are two factors to consider:

• 1 The `IF` formula does not distinguish between values 0-5 and 6-10.
• 2 When the results of the `IF` formula are concatenated (such as `=C2&""&C3`), the numeric answers are converted to a string. However the values in the search column used for `vlookup` are numbers; so the `VLOOKUP` formula is searching for a string in a column of numbers.

These can be resolved by:

• using `AND` in the criteria of the `IF` function. "0-5", for example, is a test for a value equal to or greater than 0 `AND` equal to and less than 5.
• using `VALUE` to convert the concatenated string to a number that can be used in `VLOOKUP`.

Try this formula in, say Cell E2: `=vlookup(value(if(and(B2>=0,B2<=5),1,2)&if(and(C2>=0,C2<=5),1,2)),Sheet1!\$J\$1:\$K\$4,2,false)`

LOGIC

Responses

• Cell B2: question#1 answer = 3
• Cell C2: question#2 answer = 3

Conversion 0-5 = 1 6-10 = 2

• formula for Q#1 = `=if(and(B2>=0,B2<=5),1,2)` = 1
• formula for Q#2 = `=if(and(C2>=0,C2<=5),1,2)` = 1

Combinations Combine Q1 and Q2 converted values: `=if(and(B2>=0,B2<=5),1,2)&if(and(C2>=0,C2<=5),1,2)`

• Result = `11` BUT this value is now a string

Convert string to value

• use `VALUE`
• `value(if(and(B2>=0,B2<=5),1,2)&if(and(C2>=0,C2<=5),1,2))`
• the value is now a number

Lookup the meaningful text

• use `VLOOKUP`
• assume the `range` is on Sheet1, J1:K4
• `=vlookup(value(if(and(B2>=0,B2<=5),1,2)&if(and(C2>=0,C2<=5),1,2)),Sheet1!\$J\$1:\$K\$4,2,false)`

Data VLOOKUP Table