What is the simplest way to use a formula to detect if google sheets or microsoft excel is running, and execute the compatible formula accordingly?

0
votes

Some formulas I'm using only work in Microsoft Excel but not Google Sheets, or a different formula that has the exact same result works in Google Sheets but not Microsoft Excel.

Is there a simple way to detect if Google Sheets or Microsoft Excel is running, and execute the compatible formula accordingly?

I am finding it difficult to believe this question hasn't been asked before but I couldn't find my specific question of the same file working in both Excel and Google Sheets.

I'm not concerned about scripts, just formulas.

2
excelgoogle-sheetsexcel-formula
What are the formula(s)? It would be better to homogenize the formulas to work in both platforms and it almost certainly could be done. – user4039065
depends on the version of excel and version of Google Sheets webapps.stackexchange.com/questions/15458/… but I think that most general Excel formulas work in Google Sheets and most general Google Sheets formulas don't work in Excel – Slai
For example =ISERR(-"1pm") results in TRUE in Excel and FALSE in Google Sheets – Slai
for the last non-empty cell in Excel, it's LOOKUP(2,1/([range]<>"") where in Google Sheets its something like =DAYS360(A2; INDEX(A:A; MATCH(99^99;A:A; 1))) – user7270248
@Slai That's exactly what I was expecting, however I was hoping for a string return of "Microsoft Excel" or "Google Sheets" - perhaps this is wishful thinking, however info("RELEASE") returns the version number without "Excel" but this only works in Excel, not Google sheets. – user7270248

2 Answers

1
votes

There is no official way to determine if Microsoft Excel or Google Sheets is running through formulas, however with some versions of Excel =INFO("RELEASE") will return the version of Excel running, but this doesn't work in Google Sheets.

Thanks to @Slai, =ISERR(-"1pm") results in TRUE in Excel and FALSE in Google Sheets. Excel doesn't understand how to apply math to a time string value, where Google Sheets does, hence the error.

A simple IF statement using this logic can be =IF(ISERR(-"1pm"),"MS-Excel_formula","G-Sheets_formula")

If the formula you're using returns a number, a more simplified version is =IFERROR(GoogleFormula+"0am", ExcelFormula) however this won't work for non-numeric returned formulas because it adds 0 to the result which should not change a numeric result. For instance, =IFERROR(1+"0am", 2) returns 1 in Google Sheets and 2 in Excel.

If Microsoft decides to recognise "1am" or "0am" as numeric time values in future versions of Excel, the above if statements won't work as intended and Excel will attempt to execute the Google Sheets formula.

0
votes

One boolean solution is to force an error in a function you absolutely know doesn't work in one or the other program. So @Slai 's example would work well no? Also, excel doesn't attempt to implement the Google only functions like ArrayFormula or Importrange &c. FWIW The minor discrepancies are the ones that catch me out - 'DSUM' for example frustrates me in that Google Sheets responds slightly differently to the Excel implementation under certain conditions. There will be others I'm sure but I've not yet found a definitive list or collation thread of these. (PS. I've not expanded my description of the DSUM idiosyncrasy as I suspect it'll be too far off topic).