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
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