Search code examples
google-sheetsaveragearray-formulascurrency

Find the AVERAGE of Multiple Dollar Amounts in Row, Skipping Blank Cells, with an ArrayFormula (or similar) in Google Sheets

I don't know why I'm having so much trouble with this, but I basically need a version of this...

=AVERAGE(A2:L2)

...But to put into something like an ArrayFormula in the header row so it will populate the average for each Row A:L, and place that average in Column M. For example...

  • M2 should give the average of all the $ dollar price currency values in Row A2:L2.
  • M3 should give the average of all the $ dollar price currency values in Row A3:L3.
  • Etc.

Unfortunately, it doesn't seem like I can just pop "AVERAGE" into an ArrayFormula. Also, I need it to skip blank cells when determining the AVERAGE. Here's a demo of what I'm hoping for... (For reference, "Jan" is A1, "*Ave." is M1.)

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec *Ave.
$2.00 $4.00 $8.00 $6.00 $4.00 $8.00 $10.00 $6.00
$6.00 $10.00 $10.00 $6.00 $4.00 $2.00 $6.00 $4.00 $8.00 $6.22

*I'm hoping to place the formula in the cell M1 ("*Ave.") to produce the results for each row to be placed in that column.

I hope that all makes sense. Thanks in advance for any help!

Solution

  • You may try:

    =vstack(
       "*Ave.",
       byrow(A2:L,lambda(Σ,if(counta(Σ)=0,,average(Σ))))
       )