0

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(?!)

eg: https://docs.google.com/spreadsheets/d/19VwXMadIdCGDHtQzc3wewhBDXUYIQ4h-Y848n08kIOg/edit?usp=sharing - please only add new rows to show new options - don't edit the existing rows, so others can see the originals

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 dependant 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. How can this be corrected, please? I’m pulling our hair out here. Happy to throw all this out for a method / set of formulas that someone can give me that works.

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.

Any ideas on what the answer would be? Feel free to create new rows (not edit existing ones) to demonstrate a working method.

Cheers, Kurt

4
  • 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.
    – Tedinoz
    Sep 30 '21 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 '21 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).
    – Tedinoz
    Oct 1 '21 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.
    – Tedinoz
    Oct 1 '21 at 10:26
0

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

Data


VLOOKUP Table

vlookuptable

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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