CODEWITHSUNDEEP
google-sheetsaveragearray-formulascurrency
Computer Candy
Computer Candy

Reputation: 57

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...

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!

Upvotes: 0

Views: 109

Answers (2)

DataBunny
DataBunny

Reputation: 185

Google sheets AVERAGE() ignores blank values. So

=BYROW(A2:L3,LAMBDA(i,AVERAGE(i)))

does what you need.

Upvotes: 1

rockinfreakshow
rockinfreakshow

Reputation: 30250

You may try:

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

Upvotes: 3

Related Questions