Annual average balance with varying number of periods in list

[Ann164]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Hi, I have a list of accounts and monthly balances an some averages to calculate below. I was thinking a combination of SUMIF, COUNTIF and SUMPRODUCT may work but I am not sure how to structure. Does anyone have some views based on table below? I would be ok if all accounts had a balance365 days in year or at least 12 months. The data list is currently 16,000 lines for 24 months, I can split into years.[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 448, align: left"]
<tbody>[TR]
[TD="width: 312, bgcolor: transparent, colspan: 2"]Average monthly balance for the quarter[/TD]
[TD="width: 93, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A1[/TD]
[TD="bgcolor: transparent"]+sum(balance)/nperiods in qtr[/TD]
[TD="bgcolor: transparent"]=1000/3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A2[/TD]
[TD="bgcolor: transparent"]+sum(balance)/nperiods in qtr[/TD]
[TD="bgcolor: transparent"]=(1200+900+750)/3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A3[/TD]
[TD="bgcolor: transparent"]+sum(balance)/nperiods in qtr[/TD]
[TD="bgcolor: transparent"]=(900+1200+1500)/3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A4[/TD]
[TD="bgcolor: transparent"]+sum(balance)/nperiods in qtr[/TD]
[TD="bgcolor: transparent"]=(800+2000)/3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 3"]Average monthly balance for the number of periods where balance is > nil and then how many periods was the average based on in the calculation.

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A1[/TD]
[TD="bgcolor: transparent"]=number of periods held in qtr[/TD]
[TD="bgcolor: transparent"]+1000/1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A2[/TD]
[TD="bgcolor: transparent"]=number of periods held in qtr[/TD]
[TD="bgcolor: transparent"]=(1200+900+750)/3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A3[/TD]
[TD="bgcolor: transparent"]=number of periods held in qtr[/TD]
[TD="bgcolor: transparent"]=(900+1200+1500)/3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A4[/TD]
[TD="bgcolor: transparent"]=number of periods held in qtr[/TD]
[TD="bgcolor: transparent"]=(800+2000)/2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 3"]Average balance over a year regardless of the number of months where balance is > nil[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 3"]Weighted average of account balance for each over year based on number of months where balance is > nil

Annual average of total of all accounts[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 304"]
<tbody>[TR]
[TD="width: 117, bgcolor: transparent"]Date[/TD]
[TD="width: 195, bgcolor: transparent"]Account[/TD]
[TD="width: 93, bgcolor: transparent"]Balance[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20130131[/TD]
[TD="bgcolor: transparent"] A1[/TD]
[TD="bgcolor: transparent, align: right"]1000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20130131[/TD]
[TD="bgcolor: transparent"] A2[/TD]
[TD="bgcolor: transparent, align: right"]1200[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20130131[/TD]
[TD="bgcolor: transparent"] A3[/TD]
[TD="bgcolor: transparent, align: right"]900[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20130131[/TD]
[TD="bgcolor: transparent"] A4[/TD]
[TD="bgcolor: transparent, align: right"]800[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20121231[/TD]
[TD="bgcolor: transparent"] A2[/TD]
[TD="bgcolor: transparent, align: right"]900[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20121231[/TD]
[TD="bgcolor: transparent"] A3[/TD]
[TD="bgcolor: transparent, align: right"]1200[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20121231[/TD]
[TD="bgcolor: transparent"] A4[/TD]
[TD="bgcolor: transparent, align: right"]2000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20121130[/TD]
[TD="bgcolor: transparent"] A2[/TD]
[TD="bgcolor: transparent, align: right"]750[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20121130[/TD]
[TD="bgcolor: transparent"] A3[/TD]
[TD="bgcolor: transparent, align: right"]1500[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[LxQ]

If account number is in col B and balances in col C, you can get the average balances over reported periods with this formula:

=SUMIF($B$2:$B$10,$B2,$C$2:$C$10)/COUNTIF($B$2:$B$10,$B2)