+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Registered User
    Join Date
    09-30-2021
    Location
    Paris, France
    MS-Off Ver
    Google Sheets
    Posts
    2

    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. #2
    Forum Contributor
    Join Date
    12-17-2013
    Location
    ON, Canada
    MS-Off Ver
    MS 365
    Posts
    119

    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.

    https://docs.google.com/spreadsheets...it?usp=sharing

  3. #3
    Registered User
    Join Date
    09-30-2021
    Location
    Paris, France
    MS-Off Ver
    Google Sheets
    Posts
    2

    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
    Last edited by Kurtsmyname; 10-01-2021 at 07:59 AM. Reason: additional problem solved

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Google Sheets - Mandatory fields to fill in
    By Stellix in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-14-2021, 05:13 AM
  2. Convert Google sheets formulae to excel
    By TH1963 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-28-2020, 03:56 AM
  3. [SOLVED] Google Sheets Formulas - How do I to trap #VALUE & #N/A errors efficiently
    By stewart@itsit.com.au in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 09-01-2020, 04:13 AM
  4. Google Sheets: Ignore Errors and Calculate Average
    By Manikandan Arumugam in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 13
    Last Post: 06-07-2020, 12:39 AM
  5. Formulas not working with imoprted data from google sheets
    By gassiusmax in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-16-2018, 03:19 PM
  6. Google Apps Script for Google Sheets Pulling Formulas from Master to Several Slave Sheets
    By excelroofing in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 08-22-2018, 02:06 AM
  7. From Excel working to Google Sheets no longer working
    By rad1964 in forum Excel General
    Replies: 5
    Last Post: 07-19-2016, 10:23 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1