If x is y then z, else x without repeating x in Google Sheets

user_5160927 0
Viewed 82

In a cell, is it possible to do if(x=y, z, x) without having to repeat x in the value_if_false argument? Whether there is a way of using if() to make this work or another function doesn't matter, and there isn't a specific formula I'm struggling with as I come across this blocker quite often (hence posting).

To help illustrate the need, if we take x as a complex or more advanced formula, such as

ARRAYFORMULA(IF(E$6:Q$6 < EoMONTH($P$4,0), "Not Active", IF(E$6:Q$6<$Q$4 + ISBLANK($Q$4) > 0,
COUNTIF({'Data'!$B$3:$B&'Data'!$I$3:$I&'Data'!$K$3:$K},$B$4&$C9&E$6:Q$6), "Not Active")))

and I wanted to put an if statement in there that changed the result only if a condition was true, the formula would more than double in size due to having to reference x twice:

=ARRAYFORMULA(IF(IF(E$6:Q$6 < EoMONTH($P$4,0), "Not Active", IF(E$6:Q$6<$Q$4 + ISBLANK($Q$4) > 0,
COUNTIF({'Data'!$B$3:$B&'Data'!$I$3:$I&'Data'!$K$3:$K},$B$4&$C9&E$6:Q$6), "Not Active"))) = 0, "No data", IF(E$6:Q$6 < EoMONTH($P$4,0), "Not Active", IF(E$6:Q$6<$Q$4 + ISBLANK($Q$4) > 0,
COUNTIF({'Data'!$B$3:$B&'Data'!$I$3:$I&'Data'!$K$3:$K},$B$4&$C9&E$6:Q$6), "Not Active"))))

This is just an example (the code is irrelevant), I'm trying to keep my formulas neat, tidy and efficient so that handing off to others is easier. Then I'm also mindful that it is calculating the same complex formula twice, which would probably slow the spreadsheet down especially when iterated throughout a spreadsheet.

Interested to hear the community thoughts and suggestions on this, hopefully I was clear in explaining it. :)

google-sheets-formula google-apps-script google-sheets if-statement dry
2 Answers

The only simple way to achieve this would be with the use of helper columns. They don't need to be in the same sheet as your main equation, but they do need to be within that same spreadsheet as a whole (ie you could have a sheet named "calc" that's specifically used to calculate intermediate steps and set "variables" by referencing those cells).

The only other option (which gets a bit complicated) is to create a custom function within Google Apps Script. For example, if you wanted to calculate (B1*A4)/C5 in multiple places, you could create a custom function like this:

/**
* Returns a calculation using cells A4, B1, and C5.
* @return A calculation using cells A4, B1, and C5.       
* @customfunction
*/
function x() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('MainSheet');
  var val1 = ss.getRange('B1').getValue();
  var val2 = ss.getRange('A4').getValue();
  var val3 = ss.getRange('C5').getValue();

  return (val1*val2)/val3;
}

Then in your sheet, you could use this within a formula like this:

=if(A1="yes", x(), "no")

This custom function could obviously be altered to fit one's needs (ex taking in arguments to define the cells that the calculations should be done on instead of hard coding them, etc).

Other than this, there is currently no way to define variables within a formula itself.

user_118612180

This is possible to a certain extent, using TEXT's Meta Instructions, if you're using numbers and simple math conditions.

x y z output
10 10 5 10 
=TEXT(A3,"[="&B3&"]0;"&C3&"")
x y z output
11 10 5 5

As long as your complex formula returns a number for x(or the output can be coerced to a number), this should be possible and it avoids repetition.

user_84044530
Related