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

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. :)

CodePudding user response：

The 'best' answer to this would be that Google add the LET function that Excel 365 has to Sheets, then you could wrap your IF inside this, refer to your complex expression once, give it a short name and then use the short name twice in the IF statement. However in the meantime the only way of dealing with this (other than the helper column idea and only in some contexts) is the use of IFERROR, and reworking the logic of your IF statement so that it generates an error for certain results that you then trap - very difficult to elaborate without knowing the specific context.

CodePudding user response：

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.