Get average balance of date range broken down by month

[mkseto]

I have 2 sheets:
Sheet "BALANCES" contains daily account balances: top row are sequential dates from Oct/01/2024 to Feb/28/2025 starting from cell B1, first column are 7-digit account numbers starting from cell A2
Sheet "AVE_BAL"

- Column "B" = 7-digit account numbers starting from cell B2​

- Column "L" = STARTING DATES starting from cell L2​

- Column "AC" = END DATES starting from cell AC2​

​

I'm trying to build a formula in sheet "AVE_BAL":

- in column "W": for each account in column "B", retrieve the daily balances between the "STARTING DATE" and "END DATE" (inclusive) for dates within the month of Oct/2024, determine the average balance (note: exclude the number of days when calculating the average for days with zero balance. For example, if there are 8 days in the period but 2 of the days had zero balance, divide the total by 6 instead of 8).​

- using the same logic as above, repeat the formula for columns X/Y/Z/AA but for months of Nov/2024, Dec/2024, Jan/2025 and Feb/2025, respectively.​

​

I'm using an old version of Excel (2013) but have access to 365, if needed.
I have very limited knowledge of Excel. I started with using INDIRECT to find the total balances with each date range unsuccessfully. No idea how I could break up the totals by month. I believe I can count the non-zero amounts to calculate the average but need to build the other pieces first.
Help needed/appreciated!!

 

[mkseto]

Try pasting the following formula in cell W2 and drag-copying it down and to the right as needed:

=IFERROR(AVERAGEIFS(INDEX(BALANCES!$B:$EV,MATCH($B2,BALANCES!$A:$A,0),0),BALANCES!$B$1:$EV$1,">="&MAX($L2,W$1),BALANCES!$B$1:$EV$1,"<="&MIN($AC2,EOMONTH(W$1,0)),INDEX(BALANCES!$B:$EV,MATCH($B2,BALANCES!$A:$A,0),0),"<>0"),"")

Note: it is assumed that data in range W1:AA1 are the 1st of the months formatted as mmm/yyyy

This formula worked perfectly in the beginning. It was my mistake after mistake that kept reporting issues. Again, my deepest apology and THANK YOU for the quick and PERFECT solution!!!

 

[Tetra201]

Glad to have helped.