Replicate Google spreadsheet array formula in Excel

4
votes

I’ve got Google form survey data I’d like to analyse. In my example doc, Sheet 1 contains the survey responses and sheet 2 references the data in sheet 1. Sheet 2 is where I am using this array formula under the column called Behaviour:

=ArrayFormula(if(len(A3:A),if(Sheet1!Question3="Yes","Excluded ",)&if(Sheet1!Question5="Yes","Arrested ",)&if(Sheet1!Question6="Yes","Alcohol ",)))

The issue is that I need to be able to download this Google spreadsheet as a Microsoft Excel doc but whenever I do that, of course the Google spreadsheet =ArrayFormula() function is not supported in Microsoft Excel.

My question is how could I replicate this array function’s behaviour using another method in Google spreadsheet so that I get the same result when it’s downloaded to Microsoft Excel?

What I’ve been able to find in the forum so far are questions on how you can manually copy formulas to multiple rows in Excel. The trouble with these methods are that the user has to do it by either clicking and dragging the bottom right-hand corner of the cell or by using shortcuts. I’d like my process to work so that the user doesn’t have to manually do anything.

Many thanks in advance.

3
excelgoogle-apps-scriptgoogle-sheets
It does translate fine into Excel except that when any of the three inner if statements fail you get a zero - need to change them into if(Question3="Yes","Excluded","") etc. and also change A3:A into A3:1000 – Tom Sharpe

3 Answers

0
votes

Excel support array formulas but it does that in a different way than Google Sheets.

Instead of an ARRAYFORMULA function, Excel requires that the formula autor/editor, first selects the area to which the array formula result should be extended, then enter edit mode and press CTRL+SHIFT+ENTER. See Create an array formula that calculates multiple results on Create an array formula

0
votes

It is possible to replicate the Google Sheet ArrayFormula in MS Excel using the OFFSET() formula even if it's not as simple as the ArrayFormula function.

The full documention of the function can be found here.

https://support.office.com/en-us/article/OFFSET-function-C8DE19AE-DD79-4B9B-A14E-B4D906D11B66

A quick example

Google Sheet

= ARRAYFORMULA(SQRT(Sheet2!A:A))

=> This will copy the full column A for the Sheet2 taking the square root of each value.

Excel Version

= SQRT(OFFSET(Sheet2!A1,0,0,COUNTA(Sheet2!A:A),1))

=> This will take a reference of the column, but we have to manually specify the height and width of the reference before taking the square root of each value.

-2
votes

You may very well discover that this is not possible; at least not in every case. There may be a few formulae which have the exact same definition in Google sheets and Excel. however, it's not guaranteed to work in general.

Some more detailed information here: Sheets vs Excel forumlae