Get average balance of date range broken down by month

mkseto

New Member
Joined
Aug 14, 2018
Messages
45
Office Version
  1. 365
  2. 2013
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!!
 
Did a sample format,
Book3
ABCDEFGHIJKLMNOPQR
1Acc No.Acc No.10/1/202410/2/202410/3/202410/4/202410/5/202410/6/202410/7/202410/8/202410/9/202410/10/202410/11/202410/12/202410/13/2024Avr Bal
212345671234567133145157169181193205217229241253265277205
312345791234579145157169181193205217229241253265277289217Start Date10/1/2024
412345911234591157169181193205217229241253265277289301229End Date10/13/2024
512346031234603169181193205217229241253265277289301313241
612346151234615181193205217229241253265277289301313325253
712346271234627193205217229241253265277289301313325337265
812346391234639205217229241253265277289301313325337349277
912346511234651217229241253265277289301313325337349361289
1012346631234663229241253265277289301313325337349361373301
Balances
Cell Formulas
RangeFormula
P2:P10P2=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")
 
Upvote 0
Try pasting the following formula in cell W2 and drag-copying it down and to the right as needed:
Excel Formula:
=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
 
Upvote 0
Try pasting the following formula in cell W2 and drag-copying it down and to the right as needed:
Excel Formula:
=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.
 
Upvote 0

Forum statistics

Threads
1,226,837
Messages
6,193,251
Members
453,784
Latest member
Chandni

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top