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...

 

[bhmcpfs]

Barry (or anyone else who can assist), your formula gets me much closer, however, I have to also factor in debits (col F in my post), that's why I'm trying to use the 'balance' column to compute interest. CSE (Array) formula hopefully will get me there. Also, why did I get all the gibberish: "" on my HTML Maker post? I'd like to be sure I learn from my mistake(s). Thanks in advance.

 

[bhmcpfs]

Ok, I read the FAQ, and understand about the '' sorry in advance...

 

[barry houdini]

OK, here's an updated version

Formula in G9 is

=IF(D9="int",(SUM(IF(DATE(YEAR(C$4:C8),MONTH(C$4:C8)+1,0)=C9,(G$4:G8-F$4:F8)*(C9-(C$4:C8)+1),0)))/DAY(C9)*I9,"")

As before this is an array formula which must be confirmed with CTRL+SHIFT+ENTER

This will now take account of debits as per my example and multiply the average daily balance by the monthly percentage rate in the same row - you should be able to copy down the column - interest will only be calculated if column D says "int"

excel test.XLS
	B	C	D	E	F	G	H	I	J
1
2		Date	Desc		Debit	Credit	Balance	Interest rate
3
4		01/01/2005	Initial Dep		$20.00	$30.00	$10.00
5		05/01/2005	Dep			$1.00	$11.00
6		10/01/2005	Dep			$4.65	$15.65
7		18/01/2005	Dep		$10.00	$17.00	$22.65
8		22/01/2005	Dep			$20.00	$42.65
9		31/01/2005	Int			$1.18	$43.83	4.95%
10
Sheet7

 

[mikeyork55]

Can you break down this formula by explaining each componet? Thank you.

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

******** ******************** ************************************************************************><center><table align="center" cellpadding="0" cellspacing="0"><tbody><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" colspan="5" bgcolor="#0c266b"><table width="100%" align="center" border="0"><tbody><tr><td align="left">Microsoft Excel - days&months.xls</td><td style="font-size: 9pt; color: rgb(255, 255, 255); font-family: caption;" align="right">___Running: 11.0 : OS = Windows XP </td></tr></tbody></table></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); height: 25px;" colspan="5" bgcolor="#d4d0c8"><table valign="MIDDLE" width="100%" align="center" border="0"><tbody><tr><td style="font-size: 10pt; color: rgb(0, 0, 0); font-family: caption;">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</td><td valign="center" align="right"><form name="formCb059465"><input *******="window.clipboardData.setData("Text",document.formFb543420.sltNb142651.value);" value="Copy Formula" name="btCb290334" type="button"></form></td></tr></tbody></table></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" colspan="5" bgcolor="white"><table border="0"><tbody><tr><form name="formFb543420"></form><td style="width: 60px;" align="middle" bgcolor="white"><select onchange="document.formFb543420.txbFb965317.value = document.formFb543420.sltNb142651.value" name="sltNb142651"><option value="{=((EOMONTH(A2,0)+1)*SUM(B2:B6)-SUM(A2:A6*B2:B6))/DAY(EOMONTH(A2,0))}" selected="selected">A9</option></select></td><td width="3%" align="right" bgcolor="#d4d0c8">=</td><td align="left" bgcolor="white"><input size="80" value="{=((EOMONTH(A2,0)+1)*SUM(B2:B6)-SUM(A2:A6*B2:B6))/DAY(EOMONTH(A2,0))}" name="txbFb965317"></td></tr></tbody></table></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200);" width="2%" align="middle">
</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>A</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>B</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>C</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>D</center></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>1</center></td><td style="border-style: solid; border-color: rgb(0, 0, 0) rgb(212, 208, 200) rgb(212, 208, 200) rgb(0, 0, 0); border-width: 0.5pt; font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>2</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">01/01/2005</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">10</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>3</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">05/01/2005</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">1</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>4</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">10/01/2005</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">4.65</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>5</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">18/01/2005</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">7</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>6</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">22/01/2005</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">20</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>7</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>8</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>9</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">23.78</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>10</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); border-width: 0.5pt; background-color: rgb(212, 208, 200);" colspan="5"><table valign="TOP" width="100%" align="left"><tbody><tr><td style="border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); border-width: 0.5pt; width: 120pt; background-color: rgb(255, 255, 255);" align="left">Sheet3</td><td>
</td></tr></tbody></table></td></tr></tbody></table>
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</center>

 

[millardo21]

I found this very helpful but how would you figure the DAB if the first transaction was not on the first of the month?

 

[navafolk]

Thank you for this formula, concisely and no need to add more intermediate calculation column.

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

******** ******************** ************************************************************************><center>[TABLE="align: center"]
<tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0c266b]#0c266b[/URL] , colspan: 5"][TABLE="width: 100%, align: center"]
<tbody>[TR]
[TD="align: left"]Microsoft Excel - days&months.xls[/TD]
[TD="align: right"]___Running: 11.0 : OS = Windows XP [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] , colspan: 5"][TABLE="width: 100%, align: center"]
<tbody>[TR]
[TD](F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout[/TD]
[TD="align: center"]<form name="formCb059465"><input *******="window.clipboardData.setData("Text",document.formFb543420.sltNb142651.value);" type="button" value="Copy Formula" name="btCb290334"></form>[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="bgcolor: white, colspan: 5"]

<tbody>
<form name="formFb543420"></form>[TD="bgcolor: white"]<select onchange="document.formFb543420.txbFb965317.value = document.formFb543420.sltNb142651.value" name="sltNb142651"><option value="{=((EOMONTH(A2,0)+1)*SUM(B2:B6)-SUM(A2:A6*B2:B6))/DAY(EOMONTH(A2,0))}" selected="">A9</option></select>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] , align: right"] = [/TD]
[TD="bgcolor: white, align: left"]<input size="80" value="{=((EOMONTH(A2,0)+1)*SUM(B2:B6)-SUM(A2:A6*B2:B6))/DAY(EOMONTH(A2,0))}" name="txbFb965317">[/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>A</center>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>B</center>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>C</center>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>D</center>[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>1</center>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>2</center>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"]01/01/2005[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>3</center>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"]05/01/2005[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>4</center>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"]10/01/2005[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"]4.65[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>5</center>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"]18/01/2005[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>6</center>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"]22/01/2005[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"]20[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>7</center>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>8</center>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>9</center>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"]23.78[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>10</center>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"] [/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] , colspan: 5"][TABLE="width: 100%, align: left"]
<tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: left"]Sheet3[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</center>