Finding Min In Calculated Pivot Table Formula
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 |
All times are GMT +1. The time now is 06:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com