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