Yearly Average Balances

[supradude]

I have a set of balances with dates in them. The dates don't follow a full 365, rather the dates listed generally have the same balance or when the balance changes a new date is listed. For example:

	A	B
1	1/3/14	$156,358.21
2	1/27/14	$156,358.21
3	2/13/14	$156,358.21
4	3/17/14	$156,358.21
5	4/11/14	$156,358.21
6	5/15/14	$221,356.33
7	5/20/14	$221,356.33
8	6/6/14	$221,356.33
9	7/15/14	$221,356.33
10	8/15/14	$221,356.33
11	9/11/14	$221,356.33
12	9/17/14	$229,897.45
13	10/10/14	$229,897.45
14	11/13/14	$229,897.45
15	12/12/14	$229,897.45

<tbody>
</tbody>

I have several years worth of data similar to the above. I have a pretty graph showing the dates and balances throughout the years, but what I want is the actual average balance for each year. When I do a simple =average(B1:B15), it gives me an average of $201,967.92. I believe this isn't properly accounting for a full 365 days worth of numbers. When I listed out the numbers on a full 365 days in excel, I get a true average for the whole year of $199,974.44.

How do I go about getting the proper average for the year without having to list a balance for every day of the year?

Furthermore, how do I do so for ever year before and after that? I was working with =AVERAGE(IF(YEAR(A1:A15)=2014,B1:B15)). This allows me to change each year in the formula for every year I need (by changing 2014 in the formula to 2015, 2016, etc.), but it is not accounting for the balances between the dates over a full 365 days. Or am I overthinking this?

 

[DRSteele]

I really hope this works. I used four helper columns, and the result is not pretty. Copy D2 to G2 downward.

	A	B	C	D	E	F	G
1				change	max date	days	total
2	1/3/2014	41642	$156,358.21	FALSE	FALSE	-41639
3	1/27/2014	41666	$156,358.21	FALSE	FALSE	-41639
4	2/13/2014	41683	$156,358.21	FALSE	FALSE	-41639
5	3/17/2014	41715	$156,358.21	FALSE	FALSE	-41639
6	4/11/2014	41740	$156,358.21	TRUE	41773	134	$20,952,000.14
7	5/15/2014	41774	$221,356.33	FALSE	FALSE	-41773
8	5/20/2014	41779	$221,356.33	FALSE	FALSE	-41773
9	6/6/2014	41796	$221,356.33	FALSE	FALSE	-41773
10	7/15/2014	41835	$221,356.33	FALSE	FALSE	-41773
11	8/15/2014	41866	$221,356.33	FALSE	FALSE	-41773
12	9/11/2014	41893	$221,356.33	TRUE	41898	125	$27,669,541.25
13	9/17/2014	41899	$229,897.45	FALSE	FALSE	-41898
14	10/10/2014	41922	$229,897.45	FALSE	FALSE	-41898
15	11/13/2014	41956	$229,897.45	FALSE	FALSE	-41898
16	12/12/2014	41985	$229,897.45	TRUE	42004	106	$24,369,129.70
17
18						Average	$199,974.44

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet17​

Worksheet Formulas Cell Formula D2 =IF(A2=MAX($A$2:$A$16),TRUE,C3<>C2) E2 =IF(D2,IF(A2=MAX($A$2:$A$16),EOMONTH(A2,0),A3-1)) F2 =IF(COUNTIF($D1:D$2,TRUE)=0,E2-EOMONTH(MIN($A1:A$2),-1),E2-LARGE($E1:E$2,1)) G2 =IF(F2>0,F2*C2,"") G18 =SUM(G2:G16)/(EOMONTH(MAX(A2:A16),0)-(EOMONTH(MIN(A2:A16),-1))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Dave Patton]

Edit for starting date etc.

Excel 2010
	A	B	C
1		31-Dec-13
2	$156,358.21	1-Jan-14	135
3	$221,356.33	15-May-14	125
4	$229,897.45	17-Sep-14	105
5		31-Dec-14
6
7		$199,772.96
8
1c

Cell Formulas
Range	Formula
C2	=B3-B1
C3	=B4-B3
C4	=B5-B4
B7	=SUMPRODUCT(A2:A4,C2:C4)/SUM(C2:C4)

 

[supradude]

@DRSteele, using you method I was able to get the correct average. Much appreciation.

@Dave Patton, I tried your method and could not reach the average balance you show. Are you intending to build off of DRSteele's formula?

 

[DRSteele]

You're welcome. I'm glad it helped.

 

[Dave Patton]

to supradude
The formula shows the weighted average for the data shown.

 

[Dave Patton]

Excel 2010
	A	B	C
1	Amount		Days
2	$156,358.21	1-Jan-14	134
3	$221,356.33	15-May-14	125
4	$229,897.45	17-Sep-14	106
5		1-Jan-15
6			365
7		$199,974.44
8		$199,974.44
9
1c

Cell Formulas
Range	Formula
C2	=B3-B2
C3	=B4-B3
C4	=B5-B4
B7	=SUMPRODUCT(A2:A4,B3:B5-B2:B4)/(B5-B2)
B8	=SUMPRODUCT(A2:A4,C2:C4)/SUM(C2:C4)