Calculate Average Balance between two dates only

[earthworm]

I need to calculate Monthly average Balance

I am able to achive it if i break down the Ending Balance of all dates and then calculate average

Is it possible to calculate average balance between opening balance and endind balance only?

Please look below example

I dont want to break the calculation for each month

I need correct average balance from opening balance and closing balance only instead of breaking it into complete month date

I have 20k+ accounts and need to calculate them in shortest possible info.

AccountStatement_Export_1_3_2022.xls
	A	B	C	D	E	F	G
2	Opening Balance			End of Day Balance
3		Total Debit	Total Credit	- 13,548.06		Opening Balance	- 13,548.06
4	01-12-2021	3,000.00	-	- 13,548.06		Closing Balance	- 145,716.06
5	02-12-2021	8,000.00	-	- 13,548.06		Average Balance
6	03-12-2021	-	-	- 13,548.06
7	04-12-2021	-	-	- 13,548.06
8	05-12-2021	-	-	- 13,548.06
9	06-12-2021	1,518.75	-	- 13,548.06
10	07-12-2021	977.00	-	- 13,548.06
11	08-12-2021	17,518.75	-	- 13,548.06
12	09-12-2021	1,000.00	8,000.00	- 21,548.06
13	10-12-2021	3,500.00	8,000.00	- 29,548.06
14	11-12-2021	2,768.75	-	- 29,548.06
15	12-12-2021	-	-	- 29,548.06
16	13-12-2021	4,787.00	-	- 29,548.06
17	14-12-2021	1,018.75	-	- 29,548.06
18	15-12-2021	3,018.75	-	- 29,548.06
19	16-12-2021	967.43	9,000.00	- 38,548.06
20	17-12-2021	3,518.75	-	- 38,548.06
21	18-12-2021	5,037.50	-	- 38,548.06
22	19-12-2021	-	-	- 38,548.06
23	20-12-2021	3,000.00	-	- 38,548.06
24	21-12-2021	1,532.57	-	- 38,548.06
25	22-12-2021	-	-	- 38,548.06
26	23-12-2021	55,954.98	105,668.00	- 144,216.06
27	24-12-2021	6,052.05	-	- 144,216.06
28	25-12-2021	-	-	- 144,216.06
29	26-12-2021	-	-	- 144,216.06
30	27-12-2021	27,307.51	-	- 144,216.06
31	28-12-2021	10,614.75	-	- 144,216.06
32	29-12-2021	1,796.00	-	- 144,216.06
33	30-12-2021	-	1,500.00	- 145,716.06
34	31-12-2021	1,000.00	-	- 145,716.06
35				- 59,110.94
Sheet2

Cell Formulas
Range	Formula
B4:C34	B4	=SUMIF(Sheet1!$A:$A,Sheet2!$A4,Sheet1!D:D)
G3	G3	=D3
G4	G4	=D34
D3	D3	=Sheet1!F3*1
D4:D34	D4	=D3-+C4
D35	D35	=AVERAGE(D3:D34)

 

[igold]

I don't know your business, but for many business's looking to calculate an Average Balance, the simple formula of

(Beginning Balance + Ending Balance) / 2

is an acceptable method of calculation.