Sumproduct based which also weights data based on date

[Guest]

I have a data in the range A710. I am trying to perform a conditional sum
in cell B2 based on Region. I know I can do a conditional sum by using:

=SUMPRODUCT(--(A7:A10=A2),B7:B10) or
=SUMPRODUCT({1;0;0;1},{10;11;12;13})

But I also want the data to be weighted based on the number of days relative
to the dates in B1:BE. So if I could do what I wanted the the values in B2
will be:

=SUMPRODUCT({1;0;0;1},{10;11;12;13},{58%, 100%,100%,67%})

Note that the percentages are calculated by takeing the the date in B1 and
subtracting it by the dates in C7:10. I want it to be able to say:
If < than 365 then Days/365, If > 365 then 1 Otherwise 0. This will allow
me to apply a weighted variable to my conditional sum.

Any ideas how to do this. Do I have to incorporate a sumproduct and an
array (CNTL-ALT-ENTER) formula?

Thanks

EM


A B C D
1 12/31/2006 12/31/2007 12/31/2008
2 Region 1 ?
3 Region 2
4
5
6 Amount Start Date
7 Region 1 10 6/3/2006
8 Region 2 11 12/31/2007
9 Region 2 12 7/31/2008
10Region 1 13 12/31/2008

 

[Guest]

Sorry, the formula should say:

=SUMPRODUCT({1;0;0;1},{10;11;12;13},{58%, 0%,0%,67%})

As the Region 2 data has not started by year-end 2006.

Sorry about that.

EM

 

[T. Valko]

{58%, 100%,100%,67%}

How did you get 67%?

If you subtract the dates from B1 the results are:
211
-365
-578
-731

If < than 365 then Days/365, If > 365 then 1 Otherwise 0.

If you use the absolute values:
211
365
578
731
shouldn't the array look like this:

{0.58,0,1,1}

Also note that 0.58 is not the true value of 211/365. It's rounded from
0.578082191780822

Biff

 

[Guest]

yes you are right. my mistake. I rounded the % to simplify. any ideas how
to incorporate into the sumproduct?

thanks

em

 

[T. Valko]

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=SUMPRODUCT(--(A7:A10=A2),B7:B10,IF(ABS(B1-C7:C10)=365,0,IF(ABS(B1-C7:C10)<365,ABS(B1-C7:C10)/365,1)))

Based on your sample data this is what you're getting:

(10*0.578082191780822)+(13*1)

Biff

 

[Guest]

sorry for the confusion. that was not very clear. I want to be able to say:

If (date-current date+1)/365 < 0 then, 0
If (date-current date+1)/365 = 0 then, 0
Otherwise, (date-current date+1)/365

(12/31/2006-12/31/2006+1)/365=0.2740%
(12/31/2006-12/31/2007+1)/365=-99.7260%
(12/31/2006-7/31/2008+1)/365=-158.0822%
(12/31/2006-12/31/2008+1)/365=-200.00%

This will give me:
{58%, 0%,0%,0%}
and if i can find a way to roll this into the sumproduct it will look like
this:
=SUMPRODUCT({1;0;0;1},{10;11;12;13},{58%, 0%,0%,0%})

sorry, I didn't realise how badly I botched up the questions.

em

 

[Bernd]

Hello,

In B2:
=SUMPRODUCT(--($A2=$A$7:$A$10),$B$7:$B$10,($C$7:$C$10-B$1)*($C$7:$C
$10>B$1)/365)

Please notice that 365 does not exactly reflect the average no of days
per year. 365.25 would be more accurate.

Regards,
Bernd