weighted average with multiple criteria

[Deborah Sellars]

Worksheet consists of columns A-f with about 5000 lines of info
col A = text of one of four types ie(CD,DAT,ARI,VAC)
Col B = dollar amounts
col c = percentages (2 digits: ranging from 0.00% to 5.75%)
Col d = dates ranging from 01-01-2007 to 5-30-2010
how to do a CSE sumproduct with multiple criteria
a simple =sumproduct(b2:b5000,c2:c5000)/sum(b2:b5000)
answer is a % labeled the weighted average
trouble is doing this with multiple criteria for each range for example:

=sumproduct(A2:a5000="CD")*(d2:d5000>=value("01-01-2007")*(d2:d500<value("01-01-2008")b2:b5000) times (A2:a5000="CD")*(d2:d5000>=value("01-01-2007")*(d2:d500<value("01-01-2008")c2:c5000) / (A2:a5000="CD")*(d2:d5000>=value("01-01-2007")*(d2:d500<value("01-01-2008")b2:b5000)

 

[juanale81]

I don't think sumproduct is a CSE formula, you would use CSE SUM to achieve the same result as a sumproduct. In your case, I think the way to go is using the formula you wrote but with sum instead of sumproduct (entering CSE of course).

{=sum((A2:a5000="CD")*(d2:d5000>=value("01-01-2007"))*(d2:d500=value("01-01-2007"))*(d2:d500=value("01-01-2007"))*(d2:d500="whatever condition"))}

One last thing, be careful to match every parenthesis.

 

[pgc01]

Hi Deborah
Welcome to the board

Please repost your formula. Use the Code button that you see in the Post Editor, so that we can see the whole formula.

I can't make sense of the date conditions that we can read

(d2:d5000>=value("01-01-2007"))*(d2:d500=value("01-01-2007"))*(d2:d500=value("01-01-2007"))

This means

The date must be after and including 1 January 2007 AND The date must be exactly 1 January 2007 AND The date must be exactly 1 January 2007

Besides repeating 2 times the last condition, the second condition makes the first one irrelevant.

The 3 conditions together are equal to:

The date must be exactly 1 January 2007


Can you please explain what you want the formula to do?

Also I don't see column C in the formula, but that's probably in the last part that we can't see.

King regards
PGC

 

[Aladin Akyurek]

Worksheet consists of columns A-f with about 5000 lines of information
column A = text of one of four types ie(CD,DAT,ARI,VAC)
Col B = dollar amounts
col c = percentages (2 digits: ranging from 0.00% to 5.75%)
Col d = dates ranging from 01-01-2007 to 5-30-2010

how to do a CSE sumproduct with multiple criteria
=sumproduct(A2:a5000="CD")*(d2:d5000>=value("01-01-2007")*(d2:d500 < value("01-01-2008")b2:b5000) times (A2:a5000="CD")*(d2:d5000>=value("01-01-2007")*(d2:d500<value("01-01-2008")c2:c5000) / (A2:a5000="CD")*(d2:d5000>=value("01-01-2007")*(d2:d500 < value("01-01-2008")b2:b5000)
I have not been able to get a formula to work - answer should be a %

=SUMPRODUCT(--(A2:A5000="CD"),--(YEAR(D2:D5000)=2007),B2:B5000,C2:C5000)/SUMPRODUCT(--(A2:A5000="CD"),--(YEAR(D2:D5000)=2007),B2:B5000)

 

[juanale81]

Hello Aladdin,

what do the two minus signs before each parenthesis do? thank you

 

[Aladin Akyurek]

Hello Aladdin,

what do the two minus signs before each parenthesis do? thank you

It has the same effect on conditionals as * between conditionals.

See for more:

http://www.mrexcel.com/board2/viewtopic.php?t=133602

 

[ethanle]

Hi All,
The formula works great. However, is it possible to include one more criteria (for example a name or something) beside the dates in the formula? If so how? Thanks.

 

[Aladin Akyurek]

Hi All,
The formula works great. However, is it possible to include one more criteria (for example a name or something) beside the dates in the formula? If so how? Thanks.

Something like...

=SUMPRODUCT(
   --(A2:A5000="CD"),
   --(YEAR(D2:D5000)=2007),
   --(E2:E5000="Lucas"),
   B2:B5000,
   C2:C5000)/
 SUMPRODUCT(
   --(A2:A5000="CD"),
   --(YEAR(D2:D5000)=2007),
   --(E2:E5000="Lucas"),
   B2:B5000)

maybe?

 

[ethanle]

Works great.. thank you so much..