Sunday, May 19, 2013

yogi_WorkAround For ArrayFormula In Excel To Work Correctly When Imported Into Google Spreadsheet


                                          Google Spreadsheet   Post  #1201
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 19, 2013
user Diana Tashjian (http://productforums.google.com/forum/?zx=ttx1g8rgpf8v#!category-topic/docs/spreadsheets/APq0Hgo1RW8)

I understand that Excel uses the ctl-shift-enter method to specify an array formula and that Google uses the function ARRAYFORMULA. I have several very large Excel spreadsheets with many array formulas that I want to upload and convert to Google spreadsheets. The array formulas do not get converted. I can't figure out a way to avoid having to manually change hundreds of array formulas in my converted Google spreasheets. Help!
---

Hi Yogi! I'm not sure I can share the spreadsheet - it has confidential data on it but let me try to get more specific.
Here's one of the array formulas the way it looks in Excel, with the little curly braces that denote an array formula in Excel:
{=SUM(IF('YTD Master'!$A$2:$A$10=ʺJanʺ,IF('YTD Master'!$K$2:$K$10=ʺAkamai/PROʺ,IF('YTD Master'!$G$2:$G$10=ʺCʺ,IF('YTD Master'!$C$2:$C$10=ʺBI/DWHʺ,'YTD Master'!$Q$2:$Q$10)))))}
Here's what the formula looks like after the spreadsheet has been converted to a Google spreadsheet (it's no longer an array formula, the curly braces have been stripped):
=SUM(IF('YTD Master'!$A$2:$A$10=ʺJanʺ,IF('YTD Master'!$K$2:$K$10=ʺAkamai/PROʺ,IF('YTD Master'!$G$2:$G$10=ʺCʺ,IF('YTD Master'!$C$2:$C$10=ʺBI/DWHʺ,'YTD Master'!$Q$2:$Q$10)))))
And here's what it really needs to look like in Google, with the ARRAYFORMULA function added:
=SUM(ARRAYFORMULA(IF('YTD Master'!$A$2:$A$10=ʺJanʺ,IF('YTD Master'!$K$2:$K$10=ʺAkamai/PROʺ,IF('YTD Master'!$G$2:$G$10=ʺCʺ,IF('YTD Master'!$C$2:$C$10=ʺBI/DWHʺ,'YTD Master'!$Q$2:$Q$10))))))
I have many, many of these formulas in my spreadsheets. I need to be able to make global changes somehow...
----------------------------------------------------------------------------------------------------------

in the following I present a WorkAround solution wherein the formula is modified in Excel so it works in Excel as well as when imported into Google spreadsheet