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('
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('
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('
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
No comments:
Post a Comment