Pivot Table Conditional Weighted Average

GoldOnion

New Member
Joined
Feb 3, 2014
Messages
1
Hello, I am looking for some advice on how to find the weighted average (using m2 & $/m2) of a set of values based on certain criteria within a pivot table.

I have a pivot table similar to the table below. Not shown is the fact that I am filtering these values by month. The reason I am doing this is beacuse I would like to create a table that I can sort by timeframe that displays the "$/m2" for each "actual category" across thousands of "Tags". I am able to count the number of distinct categories using this formula...

SUMPRODUCT(--(ISNUMBER(SEARCH("1",E3:E11))),SUBTOTAL(3,OFFSET(E3,ROW(E3:E11)-ROW(E3),0)))

and I am able to calculate the weighted average using this formula...

SUMPRODUCT(SUBTOTAL(3,OFFSET(C3:C11,ROW(C3:C11)-ROW(C3),0,1)),C3:C11,B3:B11)/SUBTOTAL(9,B3:B11)

However, I am struggling to combine the two formulas to get it to behave like I want (which is for the "category 1" to only contain C6 & C7 and average at $2.67, and "category 2" to contain C1,C2,C3,C4,C5,C8 and to average $2.08)
I am not super proficient with Excel and tried to combine these two formulas to no avail with the logic that it would search for tags in category 1 then run the weighted average formula on them. I don't think I am fully understanding what is happening inside the formula, so I can not identify what the problem is.SUMPRODUCT(--(ISNUMBER(SEARCH("1",E3:E11))),SUBTOTAL(3,OFFSET(C3:C11,ROW(C3:C11)-ROW(C3),0,1)),C3:C11,B3:B11)/SUBTOTAL(9,B3:B11)))

Any help would be appreciated. Thanks in advance.


a b c d e

Tag # m2$/m2Proj. CategoryActual Category
C1717.971.512
C2798.182.1712
C3476.742.2712
C4720.382.2612
C5804.882.171 2
C6791.843.51 1
C71018.242.031 1
C81012.62.121 2
C9665.932.221 4
Category 12.242
Category 22.245

<tbody>
</tbody><colgroup><col><col><col><col span="2"><col></colgroup>
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Watch MrExcel Video

Forum statistics

Threads
1,123,317
Messages
5,600,915
Members
414,416
Latest member
Nobu

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
Top