urosevic

Google Sheet ArrayFormula Usage

Jan 23rd, 2016
93
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. *** Google Sheet ArrayFormula Usage ***
  2.  
  3. Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.
  4.  
  5. Many array formulas will be automatically expanded into neighboring cells, obviating the explicit use of ARRAYFORMULA.
  6. To skip blank lines, I use an FILTER() check to skip rows w/o value in required cell.
  7.  
  8. ** EXAMPLES **
  9. * Remove ' at ' from date in format '2012/04/06 at 4:07 pm' located in N2 cell *
  10. =REPLACE(N2,11,4," ")
  11. =ARRAYFORMULA(REPLACE(FILTER(N2:N, NOT(ISBLANK(N2:N))), 11, 4, " "))
  12.  
  13. * Convert date/time from M2 cell to specific format (date format for WordPress) *
  14. =TEXT(M2, "YYYY-MM-ddTHH:mm:ss")
  15. =ARRAYFORMULA(TEXT(FILTER(M2:M, NOT(ISBLANK(M2:M))), "YYYY-MM-ddTHH:mm:ss"))
  16.  
  17. * Simply multiply two numbers from 'D2' and 'E2' cells *
  18. =D2*E2
  19. =ARRAYFORMULA(FILTER(D2:D*E2:E, NOT(ISBLANK(D212:D))))
RAW Paste Data Copied