Rick_Terminal
New Member
- Joined
- Aug 11, 2015
- Messages
- 2
Hi everyone,
I've got a problem where my solution just isn't good enough and I'm hoping someone will have a better idea.
The problem:
I have to report vehicle utilisation by month, by region, and by "category" (front line, second line, specialist, and average).
I need a line/bar graph that can duplicate the figures produced by the pivot table that is linked directly to the raw data. The issue here is that when there is not a utilisation figure for a particular month/type/region, graph data table will mark this as a 'false zero', where as a pivot table will know not to include it in the average formula - so getting a different result to a straight average() sum.
Unfortunately Pivot Charts are possibly not an option because the graph is on a dynamic dashboard that allows the selection "Region" on the sheet with all the other non-pivot charts - using text boxes and drop down lists/macros.
So far, the only way I can match the results using formulas by using sumif()\countif(), but it's a monster formula to do for all regions, requiring 25xsumif/countif's to return a single type for all regions for a single month!
I hope I'm missing an easy trick here.
I've got a problem where my solution just isn't good enough and I'm hoping someone will have a better idea.
The problem:
I have to report vehicle utilisation by month, by region, and by "category" (front line, second line, specialist, and average).
I need a line/bar graph that can duplicate the figures produced by the pivot table that is linked directly to the raw data. The issue here is that when there is not a utilisation figure for a particular month/type/region, graph data table will mark this as a 'false zero', where as a pivot table will know not to include it in the average formula - so getting a different result to a straight average() sum.
Unfortunately Pivot Charts are possibly not an option because the graph is on a dynamic dashboard that allows the selection "Region" on the sheet with all the other non-pivot charts - using text boxes and drop down lists/macros.
So far, the only way I can match the results using formulas by using sumif()\countif(), but it's a monster formula to do for all regions, requiring 25xsumif/countif's to return a single type for all regions for a single month!
I hope I'm missing an easy trick here.