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 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.
