23

I'm trying to create a formula in a Google spreadsheet which looks like this:

if (x < 0, x + 1, x)

It means if x is smaller than 0, return x + 1, else return x.

However, x itself is an expression, e.g. A1 + B1. So I ended up with:

if ((A1 + B1) < 0, (A1 + B1) + 1, (A1 + B1))

How can I save the expression (A1 + B1) into a temporary variable x such that I can do this? :

x = (A1 + B1);
if (x < 0, x + 1, x);

The current expression in my spreadsheet looks like this:

if(
    timevalue(Sheet1!$D10)-timevalue(min(filter(Sheet1!$D$2:$D$99,Sheet1!$A$2:$A$99=A10,Sheet1!$E$2:$E$99=E10))))
    < 0,
    1 +
    timevalue(Sheet1!$D10)-timevalue(min(filter(Sheet1!$D$2:$D$99,Sheet1!$A$2:$A$99=A10,Sheet1!$E$2:$E$99=E10))))
    ,
    timevalue(Sheet1!$D10)-timevalue(min(filter(Sheet1!$D$2:$D$99,Sheet1!$A$2:$A$99=A10,Sheet1!$E$2:$E$99=E10))))
)

I'm trying to get it to look shorter and more manageable like this:

x = timevalue(Sheet1!$D10) - timevalue(min(filter(Sheet1!$D$2:$D$99,Sheet1!$A$2:$A$99=A10,Sheet1!$E$2:$E$99=E10))));
if(
    x
    < 0,
    1 +
    x
    ,
    x
)
  • 1
    @pnuts, Hmm, variables are part and parcel of formulas. Hacking cells as temporary variables feel like a dirty solution because cells are meant for storing business data, not ram. – Pacerier Dec 20 '14 at 17:30
  • 2
    @pnuts, It doesn't matter where it's stored as long as it is not stored in the cells. Cells are meant for storing business data. – Pacerier Dec 20 '14 at 18:03
  • 1
    @pnuts, A script solution would be a possible next-best-alternative. As long as the data does not appear in a cell it's a viable solution. – Pacerier Dec 21 '14 at 13:42
7

I often end up using cells as variables for often used computations, and in fact name them using "named ranges". It makes it easier to think about the formula you are trying to develop. You can hide those cells, if you do not want them to be seen.

| improve this answer | |
  • Will take this as selected solution until a better one comes along. – Pacerier Nov 12 '15 at 0:47
6

Short Answer

At this time, Google Sheets doesn't have a feature to assign a name to a variable defined by a formula instead of a cell or range reference. To use a formula with these kinds of variables the alternative is to use custom functions.

Custom functions in Google Sheets

Custom functions are defined in Google Apps Scripts bound projects or Google Sheets add-ons. They only could be used to return values, not to automate tasks like send an email.

A custom function is like a JavaScript function and could use JSDOC to add built-in functions features like autocomplete and to display the popup formula helper. Also a custom error message could be included.

In the case presented in the question, the desired structure of the formula to be shown in a cell should have the following structure

if(x < 0, 1 + x, x)

where x could be a custom function.

The following is a simple example of a custom function that use JSDOC.

/**
* Returns the cell value of the specified row in column A of Sheet1.
* @param {number}   row_number   Input the row number.
* @return The cell value of the specified row in column A of Sheet1.       
* @customfunction
*/
function z(row_number) {
  if(typeof(row_number) != 'number') 
    throw new Error("A row number is required");
  return SpreadsheetApp
           .getActiveSheet()
           .getRange("Sheet1!A"+row_number)
           .getValue();
}

The formula using the above custom function with the structure specified in the question will look in the following way

=if(z(10) < 0, 1 + z(10), z(10))

row() could be used instead of a constant to have as input the row where the formula is placed.

In order to "emulate" the explicit formula presented in the question, the built-in formulas should be replaced by JavasScript / Google Apps Script functions.

References

| improve this answer | |
  • Would it be possible to define custom functions using Google Sheet's functions (instead of JS's)? I did not find anything in this direction so I doubt it. – anderstood Jan 22 '16 at 3:15
  • @anderstood: At this time it's not possible. See stackoverflow.com/questions/3686061/… – Rubén Jan 22 '16 at 9:38
3

I'm making some attempts at this (I agree it would be a very useful feature, especially for long formulas. Here's what I've written so far:

//array to store variables
var variables = [];

//Wrap a formula with "D_VAR0()" for later use
function D_VAR0(formula) {
  variables.push(formula);
}

//Write "VAR0" to access the 0th element of variables[] (which could be a long formula)
function VAR0() {
  return variables[0];
}

Theoretically, this would be useful for simplifying a complex cell formula such as:

=IF((A2+B2)/B3<16, (A2+B2)/B3, ((A2+B2)/B3)*45)

into this:

=IF(D_VAR0((A2+B2)/B3)<16, VAR0, VAR0*45)

Additional D_VAR#'s and VAR#'s could be written into the script to allow for multiple variable declarations and calls as needed.

However, it seems that D_VAR0 doesn't properly store the wrapped variable into the variables[] array.

If I manually enter an element in the array, VAR0 is able to access it and return it into the active cell. For example:

var variables = ["test"];

//results in the active cell being set to "test"
function VAR0() {
     return variables[0];
}

This leaves me with two questions. If the following are possible, then I think it is possible to declare and call temporary variables via a custom function:

  1. If custom functions can access array elements, can they also store elements to an array? If so, how?

  2. If custom functions can store elements to an array, are they only accessible within the same contiguous formula in the same cell? Or is it possible to access that same stored variable in a different cell? Example:

Cell A1 = D_VAR0(sum(3,5))

Cell D4 = VAR0() //would this return 8 in cell D4? It hasn't worked in my testing.

| improve this answer | |

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.