Weighted average w/ multiple text criteria excel 2010

[mischge]

hi

i'm looking to get a weighted average from a table (in 'database format') for specific rows. i'm working on excel 2010.
below might help to convey the idea of what i am trying to achieve, although the actual solution will probably differ considerably.

=if(criteria1="text";if(criteria2="text";if(criteria3="text"));

true:
SUMPRODUCT(array1;array2)/countifs(array1;criteria1="text";array1;criteria2="text";array1;criteria3="text";

false:
0)

thanks for your help!

 

[Aladin Akyurek]

ok... first let me point out that marious' table skipped a column and the header descriptions are off.

=SUMPRODUCT(
--(B2:B6="paris"),
--(C2:C6="positions"),
--(D2:D6="corn")
E2:E6,
H2:H6)/
SUMIFS(
E2:E6,
B2:B6,"paris",
B2:B6,"positions"
C2:C6,"corn")

+SUMPRODUCT(
--(B2:B6="paris"),
--(C2:C6="futures"),
--(D2:D6="corn")
E2:E6,
H2:H6)/
SUMIFS(
E2:E6,
B2:B6,"paris",
B2:B6,"futures"
C2:C6,"corn")

+SUMPRODUCT(
--(B2:B6="paris"),
--(C2:C6="options"),
--(D2:D6="corn")
E2:E6,
H2:H6)/
SUMIFS(
E2:E6,
B2:B6,"paris",
B2:B6,"options"
C2:C6,"corn")

One thing at a time... Why must B2:B6 equal both paris and positions/?

 

[mischge]

sorry, shd read
=SUMPRODUCT(
--(B2:B6="paris"),
--(C2:C6="positions"),
--(D2:D6="corn")
E2:E6,
H2:H6)/
SUMIFS(
E2:E6,
B2:B6,"paris",
C2:C6,"positions"
D2:D6,"corn")

+SUMPRODUCT(
--(B2:B6="paris"),
--(C2:C6="futures"),
--(D2:D6="corn")
E2:E6,
H2:H6)/
SUMIFS(
E2:E6,
B2:B6,"paris",
C2:C6,"futures"
D2:D6,"corn")

+SUMPRODUCT(
--(B2:B6="paris"),
--(C2:C6="options"),
--(D2:D6="corn")
E2:E6,
H2:H6)/
SUMIFS(
E2:E6,
B2:B6,"paris",
C2:C6,"options"
D2:D6,"corn")

that's why i wanted to upload a file, so we could all be on the same page...

 

[Aladin Akyurek]

sorry, shd read
=SUMPRODUCT(
--(B2:B6="paris"),
--(C2:C6="positions"),
--(D2:D6="corn")
E2:E6,
H2:H6)/
SUMIFS(
E2:E6,
B2:B6,"paris",
C2:C6,"positions"
D2:D6,"corn")

+SUMPRODUCT(
--(B2:B6="paris"),
--(C2:C6="futures"),
--(D2:D6="corn")
E2:E6,
H2:H6)/
SUMIFS(
E2:E6,
B2:B6,"paris",
C2:C6,"futures"
D2:D6,"corn")

+SUMPRODUCT(
--(B2:B6="paris"),
--(C2:C6="options"),
--(D2:D6="corn")
E2:E6,
H2:H6)/
SUMIFS(
E2:E6,
B2:B6,"paris",
C2:C6,"options"
D2:D6,"corn")

that's why i wanted to upload a file, so we could all be on the same page...

How far does this...

=SUMPRODUCT(
--(B2:B6="paris"),
--ISNUMBER(MATCH(C2:C6,{positions","futures","options"},0)),
--(D2:D6="corn")
E2:E6,
H2:H6)/
SUM(SUMIFS(
E2:E6,
B2:B6,"paris",
C2:C6,{positions","futures","options"},
D2:D6,"corn"))

us get?

 

[mischge]

couldnt get your solution to work on my table. tried a few variations but think the ISNUMBER could be the interference??
anywho... below works! thanks to both of you contributing to this thread! appreciate the help!

=SUMPRODUCT(
--(Table1[Desk]=$A$3);
--(Table1[Commodity]=$A4);
--(Table1[Type]<>"Receivables");
--(Table1[Type]<>"Prepayments");
ABS(Table1[MT])*Table1[USD /MT (MTM)]/
SUMPRODUCT(
--(Table1[Desk]='Grain Positions'!$A$3);
--(Table1[Commodity]='Grain Positions'!$A4);
--(Table1[Type]<>"Receivables");
--(Table1[Type]<>"Prepayments");
ABS(Table1[MT])))

 

[Aladin Akyurek]

couldnt get your solution to work on my table. tried a few variations but think the ISNUMBER could be the interference??
anywho... below works! thanks to both of you contributing to this thread! appreciate the help!

=SUMPRODUCT(
--(Table1[Desk]=$A$3);
--(Table1[Commodity]=$A4);
--(Table1[Type]<>"Receivables");
--(Table1[Type]<>"Prepayments");
ABS(Table1[MT])*Table1[USD /MT (MTM)]/
SUMPRODUCT(
--(Table1[Desk]='Grain Positions'!$A$3);
--(Table1[Commodity]='Grain Positions'!$A4);
--(Table1[Type]<>"Receivables");
--(Table1[Type]<>"Prepayments");
ABS(Table1[MT])))

Semi-colons, other references, ABS, etc. No wonder.

=SUMPRODUCT(
--(Table1[Desk]=$A$3);
--(Table1[Commodity]=$A4);
1-ISNUMBER(MATCH(Table1[Type],{"Receivables";"Prepayments"},0));
ABS(Table1[MT])*Table1[USD /MT (MTM)]/
SUMPRODUCT(
--(Table1[Desk]='Grain Positions'!$A$3);
--(Table1[Commodity]='Grain Positions'!$A4);
1-ISNUMBER(MATCH(Table1[Type],{"Receivables";"Prepayments"},0));
ABS(Table1[MT])))

although I also would like to ditch ABS...

 

[marious]

How far does this...

=SUMPRODUCT(
--(B2:B6="paris"),
--ISNUMBER(MATCH(C2:C6,{positions","futures","options"},0)),
--(D2:D6="corn")
E2:E6,
H2:H6)/
SUM(SUMIFS(
E2:E6,
B2:B6,"paris",
C2:C6,{positions","futures","options"},
D2:D6,"corn"))

us get?

add "{ "positions","futures","options"} to array. Thanks for sharing Aladin

 

[marious]

aladin - you're assuming too little in your paris/corn case: the formula must also check if it is a position/futures/options, while excluding receivables/prepayments (not in sample data above).

in my opinion, since "--" or "*" seems to be the same as an AND function, you need to do individual sumproduct/wtd avg funtions for each of the "types" needed in the average.

marious comes close with his formula in cell J10, with the exception of the qualifier ($C$10:$C$14<>C10, which doesnt EXCLUDE receivables/prepayments (because it wasnt a requirement in my first post).
also, the result in, for example, cell J10 is also wrong. it should be $378.33 (using marious' sample data: 1000mt x $870 + 5000mt x $280 / 6000mt = $378.33; formula should look for all rows with paris, corn, positions/futures/options but not receivables/prepayments, then do a weighted average calc using MT and mark-to-market price)

does this help to explain my problem a bit further?

1. Disregard my first post. There is an error in the data set headers don’t match column. Something went wrong there.</SPAN></SPAN></SPAN>
2. in my opinion, since "--" or "*" seems to be the same as an AND function, you need to do individual sumproduct/wtd avg funtions for each of the "types" needed in the average.</SPAN>

If you evaluate an array for example </SPAN>($C$10:$C$14<>C10)</SPAN>, “</SPAN>F9 fto evaluate , then CTL+Z to return to previous state” you will get {TRUE;FALSE;TRUE;FALSE;TRUE}, Sumproduct doesn’t only understand numbers so – or * will give you {1,0;1;0;1}, try to evaluate --</SPAN>($C$10:$C$14<>C10)</SPAN>
Mr Griving has an excellent video in this subject http://www.youtube.com/watch?v=vxpeEf8MYaY</SPAN></SPAN>

1. (1000x$300+5000x$250/6000) </SPAN>Can you clarify your formula from the original post? I think that it should be </SPAN>(1000x$300+5000x$250)/6000 Notice that I am working with (1000x$300+5000x$250)/6000 </SPAN>
2. Can you test this new formula or post a larger data set with receivables and prepayments for testing. It is difficult to have a clear picture of all the options with such a small data set<html><head><title>Excel Jeanie HTML</title></head><body>
   <!-- ######### Start Created Html Code To Copy ########## -->
   

   Excel Workbook
   	B	C	D	E	F	G	H	I	J	K	L
   9	(desk)	(type)	(commodity)	(MT)	(cost$)	(MTM$)	Average weight cost to MTM	Numerator	Denominator	5000 X250	1000MTX300
   10	paris	physical	corn	1000	300	250	258.3333333	1550000	6000	1250000	300000
   11	paris	physical	wheat	2000	280	300
   12	london	futures	corn	-3000	290	250
   13	london	options	wheat	-2000	280	230
   14	paris	futures	corn	5000	280	250
   15
   16
   17	Keywords: Boolean Logic, Arrays, SUMPRODUCT
   WeightAverage (2)

   
   ?
   ?
   <!-- ######### End Created Html Code To Copy ########## -->
   </body></html>
   I didn't have time to look into the absolute ABS issue I am just reading Aladin responses to your post.
   Thanks also to Aladin for all your help.

</SPAN></SPAN>