No. of days split into periods
 

Hi,

Here is one for you guys:

I have the following data electricity meter readings registered which I
want to split between pre-defined periods:

Data available:

15-Jan-06 78 days
30-Dec-05 300 days
14-Oct-05 200 days
30-Nov-05 45 days


Data required:

I need to split these readings into brackets and obtain the following answers:

Jan-06 Dec-05 Nov-05 Oct-05
Reading 1 15 days 31 days 30 days 2 days
etc etc

Any ideas....??

Thanks in advance
Brian

No. of days split into periods
 

Brian,

I cannot understand what you are asking. Are the readings from the same
meter and you want to work out consumption per calendar month?


--
bob777
------------------------------------------------------------------------
bob777's Profile: http://www.excelforum.com/member.php...o&userid=28504
View this thread: http://www.excelforum.com/showthread...hreadid=506703

No. of days split into periods
 

Hi Brian,

A possible solution might be (cells A!:D4, the dates in first row start
in C1):

01/01/2006 01/12/2005
15/01/2006 78 15 31
30/12/2005 300 - 30
14/10/2005 200 - -

These cells shown as formulas:
38718 =DATE(YEAR(C1),MONTH(C1)-1,1)
38732 78 =IF($A2=C$1,MIN($A2-C$1+1,$B2),0) =IF($A2=D$1,MIN(MIN(C$1,$A2+1)-D$1,$B2-SUM($C2:C2)),0)
38716 300 =IF($A3=C$1,MIN($A3-C$1+1,$B3),0) =IF($A3=D$1,MIN(MIN(C$1,$A3+1)-D$1,$B3-SUM($C3:C3)),0)
38639 200 =IF($A4=C$1,MIN($A4-C$1+1,$B4),0) =IF($A4=D$1,MIN(MIN(C$1,$A4+1)-D$1,$B4-SUM($C4:C4)),0)

Copy column D as far to the right as necessary.

HTH,
Bernd

No. of days split into periods
 

Hi Bernard,

Thanks very much for your help .... by any chance will it be possible to
provide me with an attachment illustrating your example and sending it to


Hope this is not too much of an inconvenience.

Thanks in advance,
Brian



" wrote:

Hi Brian,

A possible solution might be (cells A!:D4, the dates in first row start
in C1):

01/01/2006 01/12/2005
15/01/2006 78 15 31
30/12/2005 300 - 30
14/10/2005 200 - -

These cells shown as formulas:
38718 =DATE(YEAR(C1),MONTH(C1)-1,1)
38732 78 =IF($A2=C$1,MIN($A2-C$1+1,$B2),0) =IF($A2=D$1,MIN(MIN(C$1,$A2+1)-D$1,$B2-SUM($C2:C2)),0)
38716 300 =IF($A3=C$1,MIN($A3-C$1+1,$B3),0) =IF($A3=D$1,MIN(MIN(C$1,$A3+1)-D$1,$B3-SUM($C3:C3)),0)
38639 200 =IF($A4=C$1,MIN($A4-C$1+1,$B4),0) =IF($A4=D$1,MIN(MIN(C$1,$A4+1)-D$1,$B4-SUM($C4:C4)),0)

Copy column D as far to the right as necessary.

HTH,
Bernd