Blanks in a pivot table

[bigdan]

I have a project due at work in 2 hours so I'm hoping I can get some help before that!

I have a huge set of data with some blanks. See example below:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Order #[/TD]
[TD]Shipping Cost[/TD]
[TD]Our Charge[/TD]
[TD]Province[/TD]
[TD]Weight Class[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]$20[/TD]
[TD]$10[/TD]
[TD]ON[/TD]
[TD]0-5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD][/TD]
[TD]$10[/TD]
[TD]BC[/TD]
[TD]0-5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]$30[/TD]
[TD]$10[/TD]
[TD]PQ[/TD]
[TD]5-10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD][/TD]
[TD]$10[/TD]
[TD]ON[/TD]
[TD]5-10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD]$40[/TD]
[TD]$10[/TD]
[TD]BC[/TD]
[TD]10-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]105[/TD]
[TD][/TD]
[TD]$10[/TD]
[TD]PQ[/TD]
[TD]10-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


There's various categories which are broken down by Province and Weight Class. I need to figure out the historical averages for each of these categories. That would normally happen by dividing the total costs in each category by the number of orders in each category. That would be simple. *However* there's a number of blankets in the second column, which is throwing my numbers way off.

How do I eliminate these records from the pivot table?

 

[navic]

See if this information can help you.

 

[bigdan]

Thanks!