# Google Sheets: How can I get working formulas to convert into text fields without errors?

1. ## Google Sheets: How can I get working formulas to convert into text fields without errors?

Hi, 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 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? Many thanks, Kurt

2. ## Re: Google Sheets: How can I get working formulas to convert into text fields without erro

Hi Kurt,

If I understand your explanation, the formula in the link should work for you.

3. ## Re: Google Sheets: How can I get working formulas to convert into text fields without erro

Thanks heaps Flyboy! You darned helpful Canadian, of course. That works. The only "weakness" I anticipate is that all the text for the options is in the one field, which could get cumbersome and/or problematic if I want to scale things up, so I've made a tweak where I have replaced the text with references to source cells.

cheers, you're a legend - Kurt

There are currently 1 users browsing this thread. (0 members and 1 guests)