logo
  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

Warning: Opening Excel Files with Array Formulas in Google Sheets

September 23, 2016 2 comments Article Google Apps, Google Sheets, Intermediate

From time to time, you might have to work with both Excel and Google Sheets interchangeably.

Generally, opening fairly standard Excel Files in Google Sheets works fine – Google has strong support for most Excel functions.

More complex or large worksheets generally fail to open in Google Sheets – not ideal, but at least there’s no hiding from the fact it doesn’t support your file – it just won’t open

However, I’ve recently come across a scenario whereby Google Sheets does open and display an Excel file – but doesn’t calculate the formula correctly and so displays incorrect data.

In a file sent to me recently, a user had created a SUMIF formula, but because he needed to allow multiple text values in the condition, he created it as an Array Formula

=SUM(SUMIFS(RAWDATA[_31_plus],RAWDATA[Date],CURRDATE,RAWDATA[Region],REGION,RAWDATA[Status],{"01","02","03"}))

As you can see, this gives a ‘Final’ figure of 6,055,859

When I receive this file (as an Excel File) via a Gmail / Google Apps linked account, I have the option of ‘previewing’ the file. And here everything looks great too

However, things go a little awry when we open the file into Google Sheets

As you can see, all my figures now differ from the one I was expecting.

Now, the cause of this difference is fairly straightforward – whilst most Functions are cross-compatible between Excel and Google Sheets, Array Formulas is one area where they differ in approach.

Rather than converting my formula, or even not display it, Google Sheets is instead trying to process the function ignoring the array – so instead of matching against values ’01’,’02’,’03’, it’s only matching value ’01’

Again, the fix (in this case) is quite simple – we converted the text values into numeric ones and re-wrote out formula without the Array.

But the real issue here is that Google Sheets should recognise that it’s receiving an Excel Array Formula, and whilst it would be nice if it could ‘correct’ it, the next best option would be to fail safe and not show the value, so at least we’d not run the risk of people looking at the values and making incorrect decisions.

Related

Tags: Arrays, Conditional, Excel, Functions, Google, Google Sheets

2 comments

  • Tyler Nading November 8, 2016 at 6:37 pm - Reply

    I’m having a similar problem where a colleague is generating a report with array formulas in Excel 2010. When they send to me and I open in Excel 2013, the array formulas are gone, similar to what you note occurs when opening in Google Sheets. Have you seen this difficulty between versions? Any recommendation?

  • David Aldred November 9, 2016 at 3:14 pm - Reply

    No, I haven’t come across this behaviour, but the bulk of my day-to-day work is between 2010 & gSheets.

    If you have an example you could share, I’d love to take a look and see if there is an issue there.

    The particular issue I have with gSheets though is that it still reports a result for the formula – but it’s incorrect – very dangerous in a business environment.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Creating your Own Outlook Calendar Files
Using the IFS() and SWITCH() Functions to test for more than one condition in Excel 2016
Format or Remove Borders from a Slicer or Timeline in Excel
Using SUMPRODUCT to create a Conditional Weighted Average in Excel
Using RANK(), and ROW_NUMBER to Rank a field without ‘skipping’ numbers in SQL Server & Teradata
Add Labels to Outliers in Excel Scatter Charts

Meta

  • Register
  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org

Categories

Pages

  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

Copyright System Secrets 2023 - Theme by ThemeinProgress