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

 

[Sam_D_Ben]

Did a sample format,

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Acc No.	Acc No.	10/1/2024	10/2/2024	10/3/2024	10/4/2024	10/5/2024	10/6/2024	10/7/2024	10/8/2024	10/9/2024	10/10/2024	10/11/2024	10/12/2024	10/13/2024	Avr Bal
2	1234567	1234567	133	145	157	169	181	193	205	217	229	241	253	265	277	205
3	1234579	1234579	145	157	169	181	193	205	217	229	241	253	265	277	289	217	Start Date	10/1/2024
4	1234591	1234591	157	169	181	193	205	217	229	241	253	265	277	289	301	229	End Date	10/13/2024
5	1234603	1234603	169	181	193	205	217	229	241	253	265	277	289	301	313	241
6	1234615	1234615	181	193	205	217	229	241	253	265	277	289	301	313	325	253
7	1234627	1234627	193	205	217	229	241	253	265	277	289	301	313	325	337	265
8	1234639	1234639	205	217	229	241	253	265	277	289	301	313	325	337	349	277
9	1234651	1234651	217	229	241	253	265	277	289	301	313	325	337	349	361	289
10	1234663	1234663	229	241	253	265	277	289	301	313	325	337	349	361	373	301
Balances

Cell Formulas
Range	Formula
P2:P10	P2	=AVERAGEIFS(OFFSET($C2,MATCH(A2,$B2:$B$10,0)-1,0,1,COLUMNS($C2:$O2)),$C$1:$O$1,">="&$R$3,$C$1:$O$1,"<="&$R$4,OFFSET($C2,MATCH(A2,$B2:$B$10,0)-1,0,1,COLUMNS($C2:$O2)),"<>0")

 

[Tetra201]

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

 

[mkseto]

Did a sample format,

My workbook has a slight different form (my fault, should have uploaded a sample file), will see if I can replace some of the values to apply this formula. Really appreciate your help!

 

[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

Huge thanks!
For some strange reasons I'm getting all blanks, I will check to make sure the column/row reference are matching.

 

[mkseto]

Add images of the 2 sheets, hope someone can help.

 

[Tetra201]

The formula offered in Post #3 has been tested to work as intended with the data layout shown in Post #6.
Please make sure that all your dates are actual Excel dates and not date-like text strings. For example, when formatted as General, cell B1 (1-Oct-2024) should show 45566.
Also, please make sure that the start dates on the AVE_BAL sheet are not later than the corresponding end dates. Example to correct: Acct. 1122150.

 

[mkseto]

The formula offered in Post #3 has been tested to work as intended with the data layout shown in Post #6.
Please make sure that all your dates are actual Excel dates and not date-like text strings. For example, when formatted as General, cell B1 (1-Oct-2024) should show 45566.
Also, please make sure that the start dates on the AVE_BAL sheet are not later than the corresponding end dates. Example to correct: Acct. 1122150.

My apology, the dates were the problem. I fixed it and the formula works except one problem: the values are "shifted" one column. For example, what should appear under Nov somehow goes under Dec., but I could figure out what I didn't wrong, can you help again, I'm really sorry for being a pain.

 

[mkseto]

My apology, the dates were the problem. I fixed it and the formula works except one problem: the values are "shifted" one column. For example, what should appear under Nov somehow goes under Dec., but I could figure out what I didn't wrong, can you help again, I'm really sorry for being a pain.

Sorry, I meant "I could NOT figure out what I DID wrong".

 

[mkseto]

Huge thanks!
For some strange reasons I'm getting all blanks, I will check to make sure the column/row reference are matching.