Quarterly Bills assistance needed (formula, not financial!)

[GLOpro]

Hi,

I have a spreadsheet that i add data to on a quarterly basis.
I am looking for a formula that will allow me to take the estimate the forthcoming bill based on the averaged rate of increased usage multiplied by the last rate.

Take Electricity Bill for example. [$=(offpeak usage x rate)+(peak usage1 x rate1)+(peak usage2 x rate2)+ (System access charge rate3 x days)]

A1 2008q1 $500
A2 2008q2 $550
A3 2008q3 $555
A4 2008q4 $575

A5 2009q1 $520
A6 2009q2 $560
A7 2009q3 $570
A8 2009q4 $600

A9 2010q1 $600
A10 2010q2 $650

Since the bills are quarterly (seasonal) I am looking for a formula that will use ie 2008q2, 2009q2, 2010q2 used to forcast 2011q2
X= calculate the rate of average rate of increased consumption from each respective quarter.
Y= calculate the rate of average rate of increased cost kw/h from each respective quarter mulitplied by the rate of the last bill entered (in this case 2010q2).
Z= X x Y (Being the estimation of the upcoming bill for that quarter)

Whilst the above calculations are easy to formulate on a static spreadsheet,

How do i create a formula that will look automatically add the new data as its included?

thanks in advance.

 

[AlphaFrog]

If you have the quarters denoted in say column B, you could use the AVERAGEIF function if you have Excel 2007 or later. If you have Excel 2000\2003, you could use the SUMIF and COUNTIF functions.

Excel Workbook
	A	B	C	D	E	F
1	Year	Quarter	$$$			Averages
2	2008	q1	$500		Q1	$540
3	2008	q2	$550		Q2	$587
4	2008	q3	$555		Q3	$563
5	2008	q4	$575		Q4	$588
6
7	2009	q1	$520
8	2009	q2	$560
9	2009	q3	$570
10	2009	q4	$600
11
12	2010	q1	$600
13	2010	q2	$650
Sheet1

Excel 2002

Cell Formulas
Range	Formula
F2	=SUMIF($B$2:$B$50,E2,$C$2:$C$50)/COUNTIF($B$2:$B$50,E2)
F3	=SUMIF($B$2:$B$50,E3,$C$2:$C$50)/COUNTIF($B$2:$B$50,E3)
F4	=SUMIF($B$2:$B$50,E4,$C$2:$C$50)/COUNTIF($B$2:$B$50,E4)
F5	=SUMIF($B$2:$B$50,E5,$C$2:$C$50)/COUNTIF($B$2:$B$50,E5)

 

[GLOpro]

Thanks,

I will give it a try on Excel 2003.

Cheers!


BTW do you know how to apply the SUMIF and COUNTIF in OpenOffice?