Converting an array formula from MS Excel to Google Sheets

2
votes

I am at my tethers end on a question and was hoping someone on here could point out the simple mistake I am making. I have a formula that I am using in an excel spreadsheet that works fine but when I import into Google Sheets it ceases to work.

The formula that works in excel is

{=INDEX('2009-2010'!D2:D20,MIN(IF(SUBTOTAL(3,OFFSET('2009-2010'!D2,ROW('2009-2010'!D2:D20)-ROW('2009-2010'!D2),0)),ROW('2009-2010'!D2:D20)-ROW('2009-2010'!D2)+1)))}

and the way I try and use it in sheets is

=ArrayFormula(INDEX('2009-2010'!D2:D20,MIN(IF(SUBTOTAL(3,OFFSET('2009-2010'!D2,ROW('2009-2010'!D2:D20)-ROW('2009-2010'!D2),0)),ROW('2009-2010'!D2:D20)-ROW('2009-2010'!D2)+1))))

But instead of doing what it is meant to (which is displaying the top visible cell on column 'D' of another worksheet called '2009-2010') it just inserts the whole column. I have tried a couple work arounds but I am in over my head - any assistance would be fantastic!

Somewhat surprisingly I have not been able to find anything(anywhere) about someone trying to do the same function on Google Sheets...?!

1
arraysexcelgoogle-sheets

1 Answers

0
votes

This is an oldie, but if someone is still interested one could use this formula instead, in both Excel and Spreadsheet:

=INDEX('2009-2010'!D2:D20,MIN(IF('2009-2010'!D2:D20>0,ROW('2009-2010'!D2:D20)-ROW('2009-2010'!D2)+1)))
  • The IF('2009-2010'!D2:D20>0 part can also be changed to <>"" if the values are texts.

The problem is Spreadsheet doesn't treat all array formula scenarios the same way Excel does. For example Excel calculates the CountA for each item and returns an array of 0 and 1, and Spreadsheet counts the whole array and returns one count result.

This formula is also more efficient by avoiding the OFFSET volatile function.