# How to save temporary variables in Google Sheets formula?

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
)
``````
• @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. Dec 20, 2014 at 17:30
• @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. Dec 20, 2014 at 18:03
• @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. Dec 21, 2014 at 13:42
• You could at least eliminate one copy of the calculation by refactoring the conditional: `(A1 + B1) + IF((A1 + B1) < 0, 1, 0)` Mar 16, 2022 at 15:11

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.

• Will take this as selected solution until a better one comes along. Nov 12, 2015 at 0:47

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

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");
.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

• 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. Jan 22, 2016 at 3:15
• @anderstood: At this time it's not possible. See stackoverflow.com/questions/3686061/…
– Rubén
Jan 22, 2016 at 9:38
• The `LAMBDA()` function is now available in Google Sheets which supports this kind of thing without needing extra cells or AppsScript. Feb 3 at 5:35
• @SimonEast Thanks :)
– Rubén
Feb 3 at 5:39

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;
}
``````

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;
}
``````

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

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.

• Google Sheets did add the `LAMBDA()` function after your answer was written: benlcollins.com/spreadsheets/lambda-function Jan 31 at 21:14
• @DevenT.Corzine Wow, amazing. Thanks for letting me know. Will have to try this out. Have updated the answer to mention it. Feb 2 at 8:05