Re: No. of days split into periods

Hi,

Here is one for you guys:

I have the following data re: 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

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?

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

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
brian.ferris@go.com.mt

Hope this is not too much of an inconvenience.

Thanks in advance,
Brian



"bplumhoff@gmail.com" 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
>
>