Weighted average w/ multiple text criteria excel 2010

M

mischge

New Member
Joined
Jun 19, 2013
Messages
21
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!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
to illustrate my problem better, below some sample data:

(desk) (type) (commodity) (MT) (cost$) (MTM$)
paris physical corn 1000 $300 $250
paris physical wheat 2000 $280 $300
london futures corn -3000 $290 $250
london options wheat -2000 $280 $230
paris futures corn 5000 $280 $250

i need the weighted average cost$ and marked-to-market (MTM$) for each desk and commodity, i.e. weighted average cost$ for corn in paris --> (1000x$300+5000x$250/6000)

HTH!
 
Upvote 0
mischge said:
to illustrate my problem better, below some sample data:

(desk) (type) (commodity) (MT) (cost$) (MTM$)
paris physical corn 1000 $300 $250
paris physical wheat 2000 $280 $300
london futures corn -3000 $290 $250
london options wheat -2000 $280 $230
paris futures corn 5000 $280 $250

i need the weighted average cost$ and marked-to-market (MTM$) for each desk and commodity, i.e. weighted average cost$ for corn in paris --> (1000x$300+5000x$250/6000)

HTH!
Click to expand...
=(SUMPRODUCT(--($B$10:$B$14=B10),--($D$10:$D$14=D10),--($C$10:$C$14<>C10),$E$10:$E$14,$G$10:$G$14)+SUMPRODUCT(--($B$10:$B$14=B10),--($D$10:$D$14=D10),--($C$10:$C$14=C10),$E$10:$E$14,$F$10:$F$14))/SUMPRODUCT(--($B$10:$B$14=B10),--($D$10:$D$14=D10),$E$10:$E$14)
Keywords: Boolean Logic, Arrays, SUMPRODUCT, marioustx@gmail.com. Author: Mario Garcia, CTL+SHIFT+ENTER. <html><head><title>Excel Jeanie HTML</title></head><body>


<!-- ######### Start Created Html Code To Copy ########## -->


Excel Workbook
BCDEFGHIJ
9(desk)(type)(commodity)(MT)(cost$)(MTM$) Mark To MarketSumProudctDeskAverage weight cost to MTM
10parisphysicalcorn1000300250870860258.3333333
11parisphysicalwheat2000280300570860280
12londonfuturescorn-3000290250570570290
13londonoptionswheat-2000280230280570280
14parisfuturescorn5000280250280860275
15
16
17Keywords: Boolean Logic, Arrays, SUMPRODUCT
18Crude Oil (WTI)
19Crude Oil (Brent)
20RBOB Gasoline
21NYMEX Natural Gas
22NYMEX Heating Oil
23Author: Mario Garcia
24mariustx@gmail.com
WeightAverage



?

?

<!-- ######### End Created Html Code To Copy ########## -->


</body></html>
 
Upvote 0
mischge said:
to illustrate my problem better, below some sample data:

(desk) (type) (commodity) (MT) (cost$) (MTM$)
paris physical corn 1000 $300 $250
paris physical wheat 2000 $280 $300
london futures corn -3000 $290 $250
london options wheat -2000 $280 $230
paris futures corn 5000 $280 $250

i need the weighted average cost$ and marked-to-market (MTM$) for each desk and commodity, i.e. weighted average cost$ for corn in paris --> (1000x$300+5000x$250/6000)

HTH!
Click to expand...

Using the Paris and Corn case...
Rich (BB code): 
=SUMPRODUCT(--(A2:A6="paris"),--(C2:C6="corn"),D2:D6,E2:E6)/
  SUMIFS(D2:D6,A2:A6,"paris",C2:C6,"corn")
 
Upvote 0
thanks marious, thanks aladin!

for my understanding:
- how are negative numbers (MT), i.e. short positions treated? does sumproduct use absolute values?
- to string together the various criteria, you use "--". is this the same as using "*"?
- what is the difference of using sumproduct or sumifs as the denominator in the wtd avg calculation?

and here is one more complication:
in column C, there are also other variables (like receivables or prepayments), which should not go into the wtd. avg calculation. hence the qualifier ($C$10:$C$14<>C10) wont work on my table.
here's my latest formula, where i take the average of 3 independent wtd. avg calculations:

=AVERAGE

(SUMPRODUCT((Table1[Type]="Positions")*(Table1[Desk]='Grain Positions'!$A$3)*(Table1[Commodity]='Grain Positions'!$A4);Table1[MT];Table1[USD /MT (Cost)])
/SUMIFS(Table1[MT];Table1[Desk];'Grain Positions'!$A$3;Table1[Commodity];'Grain Positions'!$A4;Table1[Type];"Positions");

SUMPRODUCT((Table1[Type]="Futures")*(Table1[Desk]='Grain Positions'!$A$3)*(Table1[Commodity]='Grain Positions'!$A4);Table1[MT];Table1[USD /MT (Cost)])
/SUMIFS(Table1[MT];Table1[Desk];'Grain Positions'!$A$3;Table1[Commodity];'Grain Positions'!$A4;Table1[Type];"Futures");

SUMPRODUCT((Table1[Type]="Positions")*(Table1[Desk]='Grain Positions'!$A$3)*(Table1[Commodity]='Grain Positions'!$A4);Table1[MT];Table1[USD /MT (Cost)])
/SUMIFS(Table1[MT];Table1[Desk];'Grain Positions'!$A$3;Table1[Commodity];'Grain Positions'!$A4;Table1[Type];"Options"))

unfortunately, it gives me a DIV/0 error, if one of the 3 calcs is zero.

any ideas? (iserror around each of the 3 calcs?
is there a shorter formula?

appreciate your input!
 
Upvote 0
Instead of coming up with non-working formulas, try to word the problem you have. I already suggested a formula which covers exactly your paris and corn case. Try to elaborate the same way, that is, by way of examples based on sample data. Another note, try to build things with ordinary data, Once that is done, you can switch to Tables.
 
Upvote 0
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?
 
Upvote 0
mischge said:
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?
Click to expand...

Not at all. Specifications like position/futures/options and receivables/prepayments won't help. Instead of discussing -- vs *, try to point which ranges and conditions are needed to extend:

=SUMPRODUCT(
--(A2:A6="paris"),
--(C2:C6="corn"),
D2:D6,
E2:E6)/
SUMIFS(
D2:D6,
A2:A6,"paris",
C2:C6,"corn")
 
Upvote 0
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")
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Countifs with Multiple criteria across multiple sheets
Replies
1
Views
633
etaf
etaf
K
  • Solved
formula for the average of cells across columns with multiple conditions
Replies
4
Views
452
kckliodna
K
A
  • Solved
Comparing two excel files for matching / mismatching rows (long text strings - match() wont work
Replies
4
Views
598
ahaye
A
X
  • Question
SUM results of CHOOSECOLS with FILTER for multiple criteria
Replies
0
Views
656
xcelnovice101
X
C
  • Question
Multiple Filters with OR Criteria
Replies
0
Views
1K
CorrieAnn
C

Forum statistics

Threads
1,215,506
Messages
6,125,193
Members
449,213
Latest member
Kirbito

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top