How to save temporary variables in Google Sheets formula?

Question

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
)

Answer 1

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.

Answer 2

August 2022 update

Google Sheets added new features and functions being the most relavants for this cuestion Named Functions and LAMBDA

Named Functions allows to assign a name to a formula and to the formula parameters so complex functions could be used on multiple cells without the hasle of having to write and update the formula allowing to enter different values for each on each instance. This also is helpful for using the same variable name multiple times intead of having to manually write the corresponding value.

LAMBDA is a function that comes together with a group of helper functions. The first paramenter of LAMBDA assigns a name to a variable which is used in a formula included as the second parameter of LAMBDA.

Announcement from Google Workspace Updates

• Adding more flexibility to functions in Sheets

---

Original answer

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

• Did you know? (Custom Functions in Google Apps Script)
• Google JavaScript Style Guide

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

Answer 4

There are a few ways of achieving this:

1. The LAMBDA function

Excel has had the LAMBDA() function for quite a while, and now (as of August 2022) Google Sheets supports it too. LAMBDA() can be used for this purpose.

It looks like this:

=LAMBDA(x, IF(x < 0, x + 1, x))(A1+B1)

Basically it creates a function on the fly and then you can pass whatever parameter (or multiple parameters) into the custom function.

In this way you only have to write your initial expression A1+B1 once (rather than 3 times), and can pass that into another function for doing the IF() logic.

2. A Custom Apps Script Function

You could write the logic as a custom Apps Script function by clicking Extensions > Apps Script and adding your logic using Javascript:

function myLogic(x) {
  return x < 0 ? x + 1 : x;
}

You can then call this in your spreadsheet whenever you need it via:

=myLogic(A1+B1)

The two downsides to this are:

1. Apps Script functions are run on Google servers with a slight delay and this can be a bit annoying when dealing with large amounts of data

2. The syntax for Javascript is different to that of Excel/GSheets which is an additional learning curve. Javascript can be very powerful, however, so learning some of the basics can be useful knowledge for those more advanced requirements.

3. Use Another Cell to Store the Intermediate Value

You can use another column which could be hidden from view (or even in another sheet) to store the intermediate variable:

=A1+B1

And then just have your logic:

=IF(C1 < 0, C1 + 1, C1)

The result will show faster than an Apps Script function and this will often be more maintainable in the long run than your original formula, especially when formulas get complex.

It doesn't feel quite as clean, though, I agree, and may require that you "fill down" the intermediate formulas when new rows are added (difficult when the column is hidden or stored in another sheet), unless you make use of array formulas which can auto-populate based on the source data.

Final Tip: Using Array Formulas

As an example of using an array formula, instead of having =A1+B1 and =A2+B2 in your intermediate cells, you have only a single formula in the top cell which is:

=ArrayFormula(A1:A100+B1:B100)

This will loop through the ranges and do A1+B1, A2+B2 etc. and will populate all the necessary rows without needing to "fill down" when new data is added.

Hope this helps others.