Search code examples
arraysgoogle-sheets

google sheets and array formula not working properly

I have a sheet that is getting filled out thru a query. I an additional column I need to do some math each time a row is added. The array formula is expanding but it just repeats the sum from the first row, not using the new data.

=ARRAYFORMULA(IF(ISBLANK(B14:B),"$0.00",SUM(B14+C14)/G14))

same problem on this one:

={INDEX(Crew!$AT$12:$AZ$47, MATCH($I14,Crew!$AT$12:$AT$47, 0), MATCH($E14, Crew!$AT$12:$AZ$12, 0))}

Both formulas work with out the array.

Solution

  • ARRAYFORMULA repeats the same values because the internal part should also refer to multiple rows. However, it will not work properly with SUM. It is easier to use MAP and LAMBDA to process values row by row:

    =MAP(
  B14:B,
  C14:C,
  G14:G, 
  LAMBDA(
    s_1,
    s_2,
    d,
    IF(ISBLANK(s_1), "$0.00", (s_1 + s_2)/d)
  )
)