Array formula problem

[asa]

Hi everyone,


Excel 2010


I previously asked for help with this formula on the EXCEL-L mail list (LSOFT) but didn't get a solution.


I have a worksheet that has a table with three date/time columns:


The first column of interest, DATE, is a calendar day (i.e. September 24, 2011).

The second column, PERIOD START, is a date and time (i.e. 9-24-2011 5:00 AM)

The third column, PERIOD END, is a date and time (i.e. 9-25-2011 5:00 AM)


The array formula is in another column in the table and should calculate the intersection (overlap), in days, of the calendar day in the DATE column of the current row and the period represented by PERIOD START / PERIOD END on each row in the table, and take their sum.


In my actual data that sum will always be 1... but this version of the formula is intended to have the minimum of calculations to debug for starters. (The result for each row will ultimately factored by a quantity associated with the PERIOD in order to normalize on a quantity associated with each calendar day, before being summed.)


The formula:

=SUM(IF(AND(([@Date]+1)>=[Period Start],[@Date]<[Period End]),IF(([@Date]+1)<[Period End],[@Date]+1,[Period End])-IF([@Date]>[Period Start],[@Date],[Period Start]),0))


If the array formula is in a column called "Intersects", it should achieve the following results:

[pre][code],  Date      Period Start        Period End  Intersects  Values in array
9/4/11   9/4/11  5:00 AM   9/5/11 12:45 AM        0.79  {.79,0,0,0,0,0,0}
9/5/11   9/5/11 12:45 AM   9/6/11  5:30 AM           1  {.03,.97,0,0,0,0,0}
9/6/11   9/6/11  5:30 AM   9/7/11 12:00 PM           1  {0,.23,.77,0,0,0,0}
9/7/11   9/7/11 12:00 PM   9/8/11  7:00 AM           1  {0,0,.50,.50,0,0,0}
9/8/11   9/8/11  7:00 AM   9/9/11  4:00 AM           1  {0,0,0,.29,.71,0,0}
9/9/11   9/9/11  4:00 AM  9/10/11 10:00 AM           1  {0,0,0,0,.17,.83,0}
9/10/11  9/10/11 10:00 AM  9/11/11  3:30 AM           1  {0,0,0,0,0,.42,.58}

"Values in array" in the above is really a comment on what the array formula should be doing before summing.


The actual result of this formula is 0 for every row.


I have even broken the elements of the formula out to seperate columns for a non-array version that does the calculation for one specific row, to verify my logic is correct. It works perfectly...


I can upload a sample file if needed.


Here's the array formula with indentation so you can read it more readily --

=
SUM(
IF(
AND(
( [@Date]+1 ) >= [Period Start],
[@Date] < [Period End]
),
IF(
( [@Date]+1 ) < [Period End],
[@Date]+1,
[Period End]
) -
IF(
[@Date] > [Period Start],
[@Date],
[Period Start]
),
0
)
)[/code][/pre]
Thank you so much if you can spot my error!!!


Asa

 

[xld]

Don't array enter it.

 

[asa]

That will only calculate the current row intersect. It would be more correctly written with @ for all references in that case, but it's only a subset of the calculation this way.

 

[asa]

Thanks - btw


In the example data in my post, e.g. in row 2 the result would be 0.97 that way (see "values in array"), not 1.

 

[xld]

Try this

=SUM(IF(([@Date]+1>=[Period Start])*([@Date]<[Period End]),IF(([@Date]+1)<[Period End],[@Date]+1,[Period End])-IF([@Date]>[Period Start],[@Date],[Period Start]),0))

 

[asa]

OK! This appears to have worked!


I understand why multiplying the booleans works, but I wonder why the AND function doesn't? I suppose it must have to do with the order of evaluation...


In case it's of some added value to any forum readers, here is my "ultimate" array formula that takes this one step further by multiplying each period's portion of the date compared by another column, AMOUNT that holds a value associated with the period, before the array is summed resulting in an amount that can be associated with the DATE:


=SUM(IF(([@Date]+1>=[Period Start])*([@Date]<[Period End]),(IF(([@Date]+1)<[Period End],[@Date]+1,[Period End])-IF([@Date]>[Period Start],[@Date],[Period Start]))/([Period End]-[Period Start])*[Amount],0))


Asa