software

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 daysData 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?--
bob777
------------------------------------------------------------------------
bob777's Profile: www.excelforum.com/member.php...oamp;userid=28504
View this thread: www.excelforum.com/showthread...hreadid=506703Hi Brian,

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

01/01/200601/12/2005
15/01/200678 15 31
30/12/2005300 - 30
14/10/2005200 - -

These cells shown as formulas:
38718=DATE(YEAR(C1),MONTH(C1)-1,1)
3873278=IF($A2gt;=C$1,MIN($A2-C$1 1,$B2),0)=IF($A2gt;=D$1,MIN(MIN(C$1,$A2 1)-D$1,$B2-SUM($C2:C2)),0)
38716300=IF($A3gt;=C$1,MIN($A3-C$1 1,$B3),0)=IF($A3gt;=D$1,MIN(MIN(C$1,$A3 1)-D$1,$B3-SUM($C3:C3)),0)
38639200=IF($A4gt;=C$1,MIN($A4-C$1 1,$B4),0)=IF($A4gt;=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,
BerndHi 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 toHope this is not too much of an inconvenience.

Thanks in advance,
Brian
quot; wrote:

gt; Hi Brian,
gt;
gt; A possible solution might be (cells A!4, the dates in first row start
gt; in C1):
gt;
gt; 01/01/200601/12/2005
gt; 15/01/200678 15 31
gt; 30/12/2005300 - 30
gt; 14/10/2005200 - -
gt;
gt; These cells shown as formulas:
gt; 38718=DATE(YEAR(C1),MONTH(C1)-1,1)
gt; 3873278=IF($A2gt;=C$1,MIN($A2-C$1 1,$B2),0)=IF($A2gt;=D$1,MIN(MIN(C$1,$A2 1)-D$1,$B2-SUM($C2:C2)),0)
gt; 38716300=IF($A3gt;=C$1,MIN($A3-C$1 1,$B3),0)=IF($A3gt;=D$1,MIN(MIN(C$1,$A3 1)-D$1,$B3-SUM($C3:C3)),0)
gt; 38639200=IF($A4gt;=C$1,MIN($A4-C$1 1,$B4),0)=IF($A4gt;=D$1,MIN(MIN(C$1,$A4 1)-D$1,$B4-SUM($C4:C4)),0)
gt;
gt; Copy column D as far to the right as necessary.
gt;
gt; HTH,
gt; Bernd
gt;
gt;