Weighted Average in a Pivot Table

Hi all!!

I just previously asked a question about Pivot Tables, but completely forgot to ask about weighted averages.

I have data that comes from our customers on how they rate our Technicians in certain areas after they come out to their home. (scale 1-10)

I have a pivot table with the averages of their scores in each section.

But the problem is that it should be a weighted average, meaning every score that is a 6 or below counts as a 0. so 1-6=0, 7=7, 8=8, 9=9, 10=10.

Any ideas on how to put a weighted average into a pivot table?

We could go thru and replaced all the 1-6's with 0s, but we need to maintain the raw data, and there is A LOT of data to go thru.

Thank you all for your help in advance, you guys are always so helpful!!

Attached is an example document.

My suggestion would be to create an adjacent table that deals with the issue in the raw data, so in H3 enter formula:

=IF(C3=""," ",IF(C3<=6,0,C3)) and copy across 5 columns and down the list

Put Headings in H2 to L2.

Redo a Pivot table using these values now.

To hide any Div/0 errors that appear, Go to Table Options and check the For error values, show checkbox and leave the box blank.