I am having a problem with getting my formula to work correctly, hoping someone here can help me understand...
I have a dataset where each record consists of a start date, end date, and value. The start and end dates can fall anywhere within the month, and there could potentially be overlap in these records. My objective is to consolidate the values in to 'normalized' periods where the start and end dates coincide with the first and last days of the month.
I have the original start dates in column A, end dates in column B, and values in column C. The 'normalized' start is in column D, end in column E, and my consolidated value in column F, with the following formula (from cell F6 in this example):
The idea is that I multiply the original value by the number of days for which this overlaps the normalized period, and sum these results for all of the original periods as appropriate. The problem is that the result from the formula doesn't give the correct value. I broke the formula down into steps to check this manually, and I do get the correct answer this way.
Below is a sample data set, followed by my breakdown calculations:
And these are the steps in the breakdown calculations:
The correct answer for cell F6 should be 110.9 as in my breakdown, but the formula gives 242. Any ideas on the source of this inconsistency?
I have a dataset where each record consists of a start date, end date, and value. The start and end dates can fall anywhere within the month, and there could potentially be overlap in these records. My objective is to consolidate the values in to 'normalized' periods where the start and end dates coincide with the first and last days of the month.
I have the original start dates in column A, end dates in column B, and values in column C. The 'normalized' start is in column D, end in column E, and my consolidated value in column F, with the following formula (from cell F6 in this example):
Code:
=SUMPRODUCT(($C$3:$C$7)*($A$3:$A$7<=E6)*($B$3:$B$7>=D6)*(IF($B$3:$B$7<E6,$B$3:$B$7,E6)-IF($A$3:$A$7>D6,$A$3:$A$7,D6)+1))The idea is that I multiply the original value by the number of days for which this overlaps the normalized period, and sum these results for all of the original periods as appropriate. The problem is that the result from the formula doesn't give the correct value. I broke the formula down into steps to check this manually, and I do get the correct answer this way.
Below is a sample data set, followed by my breakdown calculations:
Code:
ACTUAL NORMALIZED
start end kWh/d start end kWh
5/13/08 6/12/08 8.7 4/1/08 4/30/08 0
6/12/08 7/14/08 3.6 5/1/08 5/31/08 165
7/14/08 8/12/08 3.3 6/1/08 6/30/08 496
8/12/08 9/11/08 2.9 7/1/08 7/31/08 [B]242[/B]
9/11/08 10/10/08 3.3 8/1/08 8/31/08 221
Code:
[1] [2] [3] [4] [5] [6] [7]
8.7 TRUE FALSE 6/12/08 7/1/08 -18 0
3.6 TRUE TRUE 7/14/08 7/1/08 14 50.90909091
3.3 TRUE TRUE 7/31/08 7/14/08 18 60
2.9 FALSE TRUE 7/31/08 8/12/08 -11 0
3.3 FALSE TRUE 7/31/08 9/11/08 -41 0
[B]110.9090909[/B]And these are the steps in the breakdown calculations:
Code:
[1] =($C$3:$C$7)
[2] =($A$3:$A$7<=E6)
[3] =($B$3:$B$7>=D6)
[4] =IF($B$3:$B$7<E6,$B$3:$B$7,E6)
[5] =IF($A$3:$A$7>D6,$A$3:$A$7,D6)
[6] = [4] - [5] + 1
[7] = [1] x [2] x [3] x [6]The correct answer for cell F6 should be 110.9 as in my breakdown, but the formula gives 242. Any ideas on the source of this inconsistency?
