Average the last bank balance of each day

[JamesonMH]

Hi all,

I'm trying to calculate an average of the final (last) bank balance for each day. There are many bank transaction lines daily (with a corresponding updated balance). Because of the volume causing so many ending balances in a single day, it skews the average that is most useful - which is the balance at the very end of the day (I can explain more of my reasoning if required).

Again, I only want the final (last) bank balance for each day. Meaning, there could be 100 lines of transactions for today, but I just need the very final/last balance for today in my AVERAGE calculation.

With this example bank statement, I would want to average only the numbers that are bolded, since they are the final balance for each day, reflecting all the activity for that day.

Any ideas?

Thanks!
James

Date	Description	Debit	Credit	Balance
11/05/2023	xyz		6,000	6,000
11/05/2023	xyz	50		5,950
11/06/2023	xyz	100		5,850
11/06/2023	xyz		10,000	15,850
11/07/2023	xyz	3,000		12,850
11/08/2023	xyz		40	12,890
11/08/2023	xyz		25,000	37,890
11/08/2023	xyz		80,000	117,890

 

[DRSteele]

Maybe this will help.
Using official Excel Tables for bank reconciliations makes things really efficient. You can move rows up and down or insert or delete rows easily and still have the balances all work out.

MrExcel posts20.xlsx
	F	G	H	I	J	K	L
8	Transaction	Date	Description	Debit	Credit	Balance	DayEndBal
9	1	11/5/2023	xyz		6,000.00	6,000.00
10	2	11/5/2023	xyz	50.00		5,950.00	5,950.00
11	3	11/6/2023	xyz	100.00		5,850.00
12	4	11/6/2023	xyz		10,000.00	15,850.00	15,850.00
13	5	11/7/2023	xyz	3,000.00		12,850.00	12,850.00
14	6	11/8/2023	xyz		40.00	12,890.00
15	7	11/8/2023	xyz		25,000.00	37,890.00
16	8	11/8/2023	xyz		80,000.00	117,890.00	117,890.00
17	Average						38,135.00
Sheet32

Cell Formulas
Range	Formula
K9:K16	K9	=N(OFFSET([@Balance],-1,0))-[@Debit]+[@Credit]
L9:L16	L9	=IF([@Transaction]=MAX(FILTER([Transaction],[Date]=[@Date])),[@Balance],"")
L17	L17	=SUBTOTAL(101,[DayEndBal])

 

[SunnyAlv]

Try This, If there’s any unclear information please let me know, thanks

Used Formula :

=INDEX($F$2:$F$9,MATCH(H2:H5,B2:B9,1)) *column " I2 "

=UNIQUE(B2:B9) *COLUMN " H2 "

 

[SunnyAlv]

or try this if u want to average that data

in H5

=AVERAGE(IFERROR(INDEX($F$2:$F$100,IFERROR(MATCH(UNIQUE(B2:B100),B2:B100,1),"")),""))

 

[JamesonMH]

SunnyAlv, this is exactly what I needed. Brilliant solution - thank you!

James

 

[JamesonMH]

Maybe this will help.
Using official Excel Tables for bank reconciliations makes things really efficient. You can move rows up and down or insert or delete rows easily and still have the balances all work out.

MrExcel posts20.xlsx
	F	G	H	I	J	K	L
8	Transaction	Date	Description	Debit	Credit	Balance	DayEndBal
9	1	11/5/2023	xyz		6,000.00	6,000.00
10	2	11/5/2023	xyz	50.00		5,950.00	5,950.00
11	3	11/6/2023	xyz	100.00		5,850.00
12	4	11/6/2023	xyz		10,000.00	15,850.00	15,850.00
13	5	11/7/2023	xyz	3,000.00		12,850.00	12,850.00
14	6	11/8/2023	xyz		40.00	12,890.00
15	7	11/8/2023	xyz		25,000.00	37,890.00
16	8	11/8/2023	xyz		80,000.00	117,890.00	117,890.00
17	Average						38,135.00
Sheet32

Cell Formulas
Range	Formula
K9:K16	K9	=N(OFFSET([@Balance],-1,0))-[@Debit]+[@Credit]
L9:L16	L9	=IF([@Transaction]=MAX(FILTER([Transaction],[Date]=[@Date])),[@Balance],"")
L17	L17	=SUBTOTAL(101,[DayEndBal])

Thanks DRSteele for providing an alternate solution. The only downside for me that this solution brings vs. the other solution is needing to add 2 helper columns. Otherwise, this is great, and I also appreciate you sharing insight on the usefulness of the Excel Tables. I will try to use them more. Cheers!