To calculate monthly and yearly gas usage between 2 random dates.

[Trent10]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Electricity (kWh)[/TD]
[TD]Gas (kWh)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/26/10[/TD]
[TD]4535[/TD]
[TD]21747[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21/12/10[/TD]
[TD]6878[/TD]
[TD]26787[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/5/11[/TD]
[TD]8900[/TD]
[TD]29001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/10/11[/TD]
[TD]10200[/TD]
[TD]32901[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/2/12[/TD]
[TD]14800[/TD]
[TD]36980[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/7/12[/TD]
[TD]18221[/TD]
[TD]39001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/27/12[/TD]
[TD]19011[/TD]
[TD]44012[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hello,
I would like to calculate monthly and yearly gas and electricity consumption between random dates?
The answer might be already there on this forum. In that case, please provide the link to the thread.

I would very much appreciate any help. Thank you in advance.

With regards,

Will

 

[Akashwani]

Hello and welcome to MrExcel.

Is this the sort of result you are looking for from the two random dates?.....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Electricity (kWh)	Gas (kWh)		Electricity (kWh)				Gas (kWh)
2	26/06/2012	4535	21747		Date1	Date2	Total		Date1	Date2	Total
3	21/12/2010	6878	26787		25/12/2010	06/06/2011	8900		25/12/2010	06/06/2011	29001
4	05/05/2011	8900	29001
5	10/09/2011	10200	32901
6	02/01/2012	14800	36980
7	07/02/2012	18221	39001
8	27/12/2012	19011	44012
9
Sheet1

I hope this helps.

Ak

 

[Trent10]

Dear AK,
Thank you for your prompt reply.
Would you please explain to me how you worked out the Date1 (E3) and Date 2 (F3)?
one minor correction - the year in cell A2 is 2010.

Is it possible to work out monthly and yearly gas & Electricity consumptions with the data I have provided?

Once again thank you for all your help.

With regards,

Will

 

[Akashwani]

Hi Will.

The dates in E3:F3 are just random dates I put in.
This is how I understand what the formula in G3 does...
Are any of the cells in ($A$2:$A$8 Greater than or equal to $E$3)*(Are any of the cells in ($A$2:$A$8 Less than or equal to $F$3)*(get the values from the cells in $B$2:$B$8 where there is a match)).
The first two parts of the formula returns either TRUE or FALSE, the * converts these to 1 or 0.
This is what the formula looks like underneath....
=SUMPRODUCT(({FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE})*({TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE})*({4535;6878;8900;10200;14800;18221;19011}))

Hopefully someone else will jump in with a clearer more accurate explanation.

Based on your sample data, can you post the results you would expect to get based on your month criteria and your year criteria?
Do you want the total for both gas and electric or individually as I have posted above?

Ak

 

[iyyi]

Hello and welcome to MrExcel.

Is this the sort of result you are looking for from the two random dates?.....

Sheet1

A

B

C

D

E

F

G

H

I

J

K

L

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Electricity (kWh)[/TD]
[TD="align: center"]Gas (kWh)[/TD]

[TD="colspan: 3, align: center"]Electricity (kWh)[/TD]

[TD="colspan: 3, align: center"]Gas (kWh)[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]26/06/2012[/TD]
[TD="align: right"]4535[/TD]
[TD="align: right"]21747[/TD]

[TD="align: center"]Date1[/TD]
[TD="align: center"]Date2[/TD]
[TD="align: center"]Total[/TD]

[TD="align: center"]Date1[/TD]
[TD="align: center"]Date2[/TD]
[TD="align: center"]Total[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]21/12/2010[/TD]
[TD="align: right"]6878[/TD]
[TD="align: right"]26787[/TD]

[TD="align: right"]25/12/2010[/TD]
[TD="align: right"]06/06/2011[/TD]
[TD="align: right"]8900[/TD]

[TD="align: right"]25/12/2010[/TD]
[TD="align: right"]06/06/2011[/TD]
[TD="align: right"]29001[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]05/05/2011[/TD]
[TD="align: right"]8900[/TD]
[TD="align: right"]29001[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]10/09/2011[/TD]
[TD="align: right"]10200[/TD]
[TD="align: right"]32901[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]02/01/2012[/TD]
[TD="align: right"]14800[/TD]
[TD="align: right"]36980[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]07/02/2012[/TD]
[TD="align: right"]18221[/TD]
[TD="align: right"]39001[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]27/12/2012[/TD]
[TD="align: right"]19011[/TD]
[TD="align: right"]44012[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula G3 =SUMPRODUCT(($A$2:$A$8>=$E$3)*($A$2:$A$8<=$F$3)*($B$2:$B$8)) K3 =SUMPRODUCT(($A$2:$A$8>=$I$3)*($A$2:$A$8<=$J$3)*($C$2:$C$8)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

I hope this helps.

Ak

I believe Trent10 meant something different.
In my opinion the right way to calculate the consumption between the 2 dates is to calculate the daily average consumption between the data of the date before the start date and the date after the end date, and then multiply it in the number of days between the 2 requested dates.
the formula for the electricity G3 is:

=(INDEX($B$2:$B$8,MATCH(F3,$A$2:$A$8)+1)-INDEX($B$2:$B$8,MATCH(E3,$A$2:$A$8)))/(INDEX($A$2:$A$8,MATCH(F3,$A$2:$A$8)+1)-INDEX($A$2:$A$8,MATCH(E3,$A$2:$A$8)))*(F3-E3)

the result is 2,059

the formula for the electricity K3 is:

=(INDEX($C$2:$C$8,MATCH(J3,$A$2:$A$8)+1)-INDEX($C$2:$C$8,MATCH(I3,$A$2:$A$8)))/(INDEX($A$2:$A$8,MATCH(J3,$A$2:$A$8)+1)-INDEX($A$2:$A$8,MATCH(I3,$A$2:$A$8)))*(F3-E3)

the result is 3,789

 

[iyyi]

the formula for the electricity G3 is:

=(INDEX($B$2:$B$8,MATCH(F3,$A$2:$A$8)+1)-INDEX($B$2:$B$8,MATCH(E3,$A$2:$A$8)))/(INDEX($A$2:$A$8,MATCH(F3,$A$2:$A$8)+1)-INDEX($A$2:$A$8,MATCH(E3,$A$2:$A$8)))*(F3-E3)

the result is 2,059

the formula for the electricity K3 is:

=(INDEX($C$2:$C$8,MATCH(J3,$A$2:$A$8)+1)-INDEX($C$2:$C$8,MATCH(I3,$A$2:$A$8)))/(INDEX($A$2:$A$8,MATCH(J3,$A$2:$A$8)+1)-INDEX($A$2:$A$8,MATCH(I3,$A$2:$A$8)))*(F3-E3)

the result is 3,789

To calculate the monthly and yearly consumption replace the (F3-E3) part of the formula with 30.5 for month and 365 for a year

 

[Trent10]

Thanks AK and iyyi.
I have a problem, both your formulas work, but which one is right?
Iyyi's formula returns the value in days and hours in spite of me formatting the cell to number.

 

[Akashwani]

Hi.

Thanks for the feedback I'm pleased you have resolved the problem.
If both formulas are working as you required, then I would suggest using the formula that you find the easiest to understand, this way, if you need to change it in the future, you should be able to do so with relative ease.

For iyyi's formula, have you tried formatting the cell to General?

Ak