Get average balance of date range broken down by month

mkseto

Board Regular
Joined
Aug 14, 2018
Messages
50
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!!
 
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
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!!!
 
Upvote 0

Forum statistics

Threads
1,226,887
Messages
6,193,513
Members
453,804
Latest member
Daniel OFlanagan

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