I have a pivot table with a calculated field for which the equation is
[Sum of Dollars / Count of Instances]. So in turn I'm rendering the
average cost for a list of items in a group. The table is set up such
that each column contains a week number and the rows contain a list of
items within a grouping. For example, I might be listing average cost
of apples, oranges, and peaches for each week under a grouping called
fruit. The next grouping is bread, where I'd display the average cost
by week for wheat, Italian, and rye.

My first issue: I'd like to be able to find the *range* within a
category. So for week one, what is the max cost of a piece of fruit
(regardless of type) minus the min cost of fruit?

My second issue: When calculating the range, I'd like to exclude any
zero values. Is this possible?

I suspect that I'll need to build a helper sheet to import data from
the pivot table, strip out any zero values, and perform the min & max
calculations, but if there's a way to perform this *within* the pivot
table I'd obviously prefer it.

If anyone knows this to be possible I'd love to hear it.

Thanks in advance.


Jim