Multi-Condition Weighted Average Formula

[TheDragonExp]

Hello,

I have a large data set and I am trying to calculate the weighted average price subject to two conditions. I know I can use SUMPRODUCT to accomplish this without the conditions, but is there anyway to make it work subject to conditions? I am hoping to be able to do this using a formula in a single cell. The table below shows an example of my data:

<tbody>
[TD="align: center"] A [/TD]
[TD="align: center"] B [/TD]
[TD="align: center"] C [/TD]
[TD="align: center"] D [/TD]

[TD="align: center"] Quarter
[/TD]
[TD="align: center"] Transaction Type
[/TD]
[TD="align: center"] Quantity
[/TD]
[TD="align: center"] Price
[/TD]

[TD="align: center"]Q1 2016[/TD]
[TD="align: center"]Trade[/TD]
[TD="align: center"]2000[/TD]
[TD="align: center"]$68.00[/TD]

[TD="align: center"]Q1 2016[/TD]
[TD="align: center"]Validation[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"][/TD]

[TD="align: center"]Q1 2016[/TD]
[TD="align: center"]Trade[/TD]
[TD="align: center"]400[/TD]
[TD="align: center"]$70.00[/TD]

[TD="align: center"]Q1 2016[/TD]
[TD="align: center"]Trade[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]$120.00[/TD]

[TD="align: center"]Q2 2016[/TD]
[TD="align: center"]Validation[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"][/TD]

</tbody>

What I would like to do is find the weighted average price for each quarter (i.e. Q1 2016, Q2 2016, etc.) of only those entries that are trades. So for one of the weighted average calculations it would include every price/quantity entry that is listed as Q1 2016 and the transaction type is "Trade". You'll notice that some transaction types don't have a price.

I've tried to play around with this formula, but I'm stuck and wondering if this is even possible... ("Price, Quantity, etc." refer to the entire column being selected): =SUMPRODUCT((Price)*(Quantity))/(SUMIFS(Quantity, Quarter, Q1 2016, Transaction Type, Trade))

Using the SUMIFS function, I am able to sum the total quantity of units that are traded in the specified period, but I have no idea how to make the SUMPRODUCT function only take the price and quantities from trades that are in the specified period.

Any assistance would be greatly appreciated!

 

[Mackers]

Hi

In general the way people use SUMPRODUCT with conditions is you multiply a Boolean (true/false) from a formula that compares a column to a desired value, such as A2:A10 = "Q1 2016".

So you say SUMPRODUCT((A2:A10="Q1 2016")*(all of your other formulas)).

The A2:A10 returns something like {TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,...}. The multiplication coerces the TRUE and FALSE into 1 and 0 so you end up with {1,1,1,1,0,0,...}. This then multiplies by your other formulas to return the normal result for TRUE and a zero for FALSE.

Hope that helps - let me know if you need anything cleared up or if I've not been clear

Mackers

 

[TheDragonExp]

Hi

In general the way people use SUMPRODUCT with conditions is you multiply a Boolean (true/false) from a formula that compares a column to a desired value, such as A2:A10 = "Q1 2016".

So you say SUMPRODUCT((A2:A10="Q1 2016")*(all of your other formulas)).

The A2:A10 returns something like {TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,...}. The multiplication coerces the TRUE and FALSE into 1 and 0 so you end up with {1,1,1,1,0,0,...}. This then multiplies by your other formulas to return the normal result for TRUE and a zero for FALSE.

Hope that helps - let me know if you need anything cleared up or if I've not been clear

Mackers

That works!! I came across posts using the SUMPRODUCT function like you described but I didn't understand how it was actually working, and thus couldn't replicate it. Thank you for taking the time to explain it to me! Cheers

 

[Mackers]

No problem. Your question was well-composed so I thought you might be the kind of person who prefers to have the understanding rather than just the answer.