Search code examples
google-sheetsgoogle-sheets-formulaaverage

Calculate average of multiple numbers split by newline character

I am attempting to calculate the average of numbers split by newline character across multiple columns:

={"Average"; {""; IFERROR(ARRAY_CONSTRAIN(MAP(D3:D, E3:E, F3:F, G3:G, H3:H, I3:I,
  LAMBDA(u, v, w, x, y, z, AVERAGE(
    AVERAGE(SPLIT(u, CHAR(10))),
    AVERAGE(SPLIT(v, CHAR(10))),
    AVERAGE(SPLIT(w, CHAR(10))),
    AVERAGE(SPLIT(x, CHAR(10))),
    AVERAGE(SPLIT(y, CHAR(10))),
    AVERAGE(SPLIT(z, CHAR(10)))
  ))
), IF(COUNTA(ALBUM_NAME_RNG) = 0, 1, COUNTA(C3:C)), 1))}}

Currently, this formula is able to calculate an average, but the value is incorrect for some rows:

example

The average of the first row should be "3.4697058824", but is "2.75" instead. However, the average for the second row is "1.9383333333", which is being calculated correctly by the formula.

Appreciate any help I can get with this one!

Here are the numbers: https://pastebin.com/nnHs4G8B

Solution

  • You may try:

    =byrow(D3:I,lambda(Σ,if(counta(Σ)=0,,average(split(join(char(10),Σ),char(10))))))

    enter image description here