formula for average daily balance

[bhmcpfs]

Am trying without success to create a formula to calculate average daily balance from a ledger that has a variable amount of entries per month. The variability of # of entries has me stumped. For example:

date, amount
1/1/2005, 10
1/5/2005, 1
1/10/2005, 4.65
1/18/2005, 7
1/22/2005, 20

Aver Daily Bal = 23.78. and I can get this easily manually, but I'd like a more automated solution. I'm trying a sumproduct angle, to no avail. Thanks in advance...

 

[Bubbis Thedog]

Research dynamic named ranges. That may help you out.

http://www.cpearson.com/excel/named.htm

Once you name the ranges, you can just use something like

=AVERAGE(Values)

 

[Brian from Maui]

Am trying without success to create a formula to calculate average daily balance from a ledger that has a variable amount of entries per month. The variability of # of entries has me stumped. For example:

date, amount
1/1/2005, 10
1/5/2005, 1
1/10/2005, 4.65
1/18/2005, 7
1/22/2005, 20

Aver Daily Bal = 23.78. and I can get this easily manually, but I'd like a more automated solution. I'm trying a sumproduct angle, to no avail. Thanks in advance...

How did you get 23.78?

 

[bhmcpfs]

23.78 is the average of the daily account balance. The sum is 737.30 (of the account balance each day), divided by 31 days (in January).

 

[Brian from Maui]

Array entered,

=AVERAGE(IF(DATE(YEAR(A2:A20),MONTH(A2:A20),1)=B1,B2:B20))

Where B1 houses the first day of the month of interest, i.e. 1/1/2005. You can either use a dynamic named range or have the range include more rows than you'd need.

For a dynamic named range, go to Insert/Name/Define enter a name, then in the Refers to enter,

=$A$2:INDEX($A:$A,MATCH(9.9999999999999E+307,$A:$A))

And use the name in the formula, instead of the row/column

 

[bhmcpfs]

Thanks Brian. Will try your formula with named ranges. Hopefully I can make this work, as it needs to be done monthly.

Bubbis, just using AVERAGE will not work, as there are missing days that need to be averaged.

Can I have dynamic named ranges based off other dynamic named range? (e.g. Feb start range will be dependent on Jan end range)

 

[Bubbis Thedog]

...just using AVERAGE will not work, as there are missing days that need to be averaged.

Could you elaborate on this?

 

[barry houdini]

Does this do it?

I'm assuming that you know the ranges and that you don't want the formula to identify those for you but only to do the calculation - perhaps that isn't right....

Assumes that all dates in A are in the same month

Formula in a9 is an array formula to be entered with CTRL+SHIFT+ENTER

=((EOMONTH(A2,0)+1)*SUM(B2:B6)-SUM(A2:A6*B2:B6))/DAY(EOMONTH(A2,0))

EOMONTH requires Analysis ToolPak

days&months.xls
	A	B	C	D
1
2	01/01/2005	10
3	05/01/2005	1
4	10/01/2005	4.65
5	18/01/2005	7
6	22/01/2005	20
7
8
9	23.78
10
Sheet3

 

[bhmcpfs]

Checkbook register.xls
	C	D	E	F	G	H
3	Date	Desc	C	Debit(-)	Credit(+)	Balance
4	1/1/2005	InitialDeposit	a		$10.00	$10.00
5	1/5/2005	Deposit	a		$1.00	$11.00
6	1/10/2005	Deposit			$4.65	$15.65
7	1/18/2005	Deposit			$7.00	$22.65
8	1/22/2005	Deposit			$20.00	$42.65
9	1/31/2005	Interest			$1.18	$43.83
Sheet1

Man, hope I got this right. Am trying to post small sample via HTML Maker. Had problems installing, running, etc. If this doesn't work, my apologies in advance. I'm looking for formula to calculate interest credit in G9. This will probably have to come from elsewhere in the file.

Barry - EOMONTH should be helpful as well

Bubbis - 10,10,10,10,11,11,11,11,11,15.65,15.65,15.65 these are the first 12 days used in calculating the aver daily balance in my sample data. Account balance for each day should be summed for the month, then averaged.

 

[barry houdini]

Incorporating Brian's logic with my earlier formula (I hope you don't mind, Brian) - Does this help?

Array formula (to be confirmed with CTRL+SHIFT+ENTER) in E7 is

=IF(D7="int",(SUM(IF(DATE(YEAR(C$2:C6),MONTH(C$2:C6)+1,0)=C7,E$2:E6*(C7-(C$2:C6)+1),0)))/DAY(C7)*H1,"")

C7 must contain last day of the month of in question

H1 contains monthly interest

Formula can be copied down - while column D does not contain "int" blank will appear.

days&months.xls
	C	D	E	F	G	H
1			Deposits	Balance	Monthly Interest	5%
2	01/01/2005	Initial Dep	$10.00	$10.00
3	01/05/2005	Dep	$1.00	$11.00
4	01/10/2005	Dep	$4.65	$15.65
5	01/18/2005	Dep	$7.00	$22.65
6	01/22/2005	Dep	$20.00	$42.65
7	01/31/2005	Int	$1.19	$43.84
8
average for month