Average Balance

[kparadise]

Hello,

I have a list of accounts. Each of these accounts is housed in a database that holds the account number, the date, and the balance. Anytime a transaction occurs, a new record for the account is created, with the new date and new balance. The table is somewhat of a running balance. What I need to do is figure out the average daily balance of the account during a specific period.

Below is the example.

	A​	B​	C​	D​	E​	F​	G​	H ​
1	ACCT_NUM​	DATE​	VALUE​		START_DATE	END_DATE		Answer: 7,509.17
2	abc123	6/26/2020	10.00		6/25/2020	6/30/2020
3	abc123	6/28/2020	15,010.00
4	abc123	6/30/2020	15,015.00
5	abc123	7/1/2020	20,012.57

Below is how the average daily balance was calculated:

6/25/2020	0.00
6/26/2020	10.00
6/27/2020	10.00
6/28/2020	15,010.00
6/29/2020	15,010.00
6/30/2020	15,015.00

Sum / 6 = 7,509.17

 

[DRSteele]

This should work as long as your software is up to date with Excel 365.

h7 = AVERAGE(IFNA(XLOOKUP(SEQUENCE(F2-E2+1,,E2),B2:B5,C2:C5,,-1),0))