Find AVERAGE of Multiple Dollar Amounts in Row, Skip Blank Cells, with an ArrayFormula (or similar) AFTER first Subtracting a Column in Google Sheets

Question

Working solution posted!

I need to factor in subtracting refunds from what is already accomplished below...

What it started with...

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

(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

Here is what worked. I put this in M1 in the example above. (With help from here: Find the AVERAGE of Multiple Dollar Amounts in Row, Skipping Blank Cells, with an ArrayFormula (or similar) in Google Sheets)...

=VSTACK("Monthly Ave.",
 BYROW(A2:L,
  LAMBDA(Σ,
   IF( COUNTA(Σ)=0,,AVERAGE(Σ)
   )
  )
 )
)

What I need now... (New column added in.)

1. I need the total refunds for the year (M) to be subtracted from the range used for the average but it needs to be subtracted PRIOR to factoring the average.
2. I still need it to ignore blanks and I still need it to divide only by the number of entries from Jan-Dec (A:L) the way that it does now, from the above example.

(For reference in this new table, "Jan" is A1, "Rfnd." is M1, "*Ave." is N1.)

Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Rfnd.	*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	$5.00	$5.67

This time, the formula will go in N1 from the example just above. I've tried to simply force in "-M2" along with some other tweaks but I can't get it to work. This is the closest I've come...

=VSTACK("Monthly Ave.",
 BYROW(A2:L,
  LAMBDA(Σ,
   IF( COUNTA(Σ)=0,,(SUM(Σ)-M2)/COUNTA(Σ)
   )
  )
 )
)

...But the "-M2" reference just makes it so that each separate row only subtract from that one single specific cell (M2) and not from M for each relative row. And using the range M2:M there breaks it.

I am super overtired (hence the over-wordy title and post) and I suspect I'm missing something simple but any help would be greatly appreciated. Thank you!

Update: Test Demo Sheet https://docs.google.com/spreadsheets/d/1peoRmUPdcFmBd6ZaZLeVOct4YFfkhuZQP0SQsZ-qzWs/edit?usp=sharing

Answer 1

Slightly amending the formula you tried to workout:

=VSTACK("Monthly Ave.",
 BYROW(A2:L,
  LAMBDA(Σ,
   IF( COUNTA(Σ)=0,,(SUM(Σ)-offset(index(Σ,,12),,1))/COUNTA(Σ)
   )
  )
 )
)