Google Sheets Variables – Save Temporary Variables in Formulas

google sheetsgoogle-apps-script

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
)

Best Answer

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.