Finding Min In Calculated Pivot Table Formula

J

jim

I didn't have much luck on another list, so I thought I'd try this one.
Any thoughts on the below would be appreciated.

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top