Annual average balance with varying number of periods in list

[Ann164]

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.
Average monthly balance for the quarter A1 +sum(balance)/nperiods in qtr =1000/3 A2 +sum(balance)/nperiods in qtr =(1200+900+750)/3 A3 +sum(balance)/nperiods in qtr =(900+1200+1500)/3 A4 +sum(balance)/nperiods in qtr =(800+2000)/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. A1 =number of periods held in qtr +1000/1 A2 =number of periods held in qtr =(1200+900+750)/3 A3 =number of periods held in qtr =(900+1200+1500)/3 A4 =number of periods held in qtr =(800+2000)/2 Average balance over a year regardless of the number of months where balance is > nil 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 <tbody> </tbody>
Date Account Balance 20130131 A1 1000 20130131 A2 1200 20130131 A3 900 20130131 A4 800 20121231 A2 900 20121231 A3 1200 20121231 A4 2000 20121130 A2 750 20121130 A3 1500 <tbody> </tbody>

<tbody>
</tbody>

 

[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)