Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Graphing the sum of an autofilter result
Let's say the data is laid out as follows:
Group 1 $20 Group 2 $20 Group 1 $20 Group 3 $20 If I filter the data by Group1, I will get two rows back. I want to graph all the rows for that group into one data series on the graph. I'm thinking I could create a temporary sum row, but there has to be an easier way? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Graphing the sum of an autofilter result
If all you want to plot is the filtered row in one series, plot the whole
table as one series. When you filter, the hidden rows will not plot, because by default, Excel does not plot hidden cells. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "ElPresidente" wrote in message ... Let's say the data is laid out as follows: Group 1 $20 Group 2 $20 Group 1 $20 Group 3 $20 If I filter the data by Group1, I will get two rows back. I want to graph all the rows for that group into one data series on the graph. I'm thinking I could create a temporary sum row, but there has to be an easier way? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Graphing the sum of an autofilter result
What I'm trying to avoid is having to create Pivots. Depending on our
data, we may have up to 30 different charts. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Graphing the sum of an autofilter result
In my first example, if I filtered by Group 1, I would want the graph
to combine the two rows in a plot showing $40, not two series showing $20 each. I know this can be accomplished using PivotCharts, but unless I'm wrong, the chart has to be linked to the pivot table. If I have 30 different graphs, it would require 30 pivot tables? In Access, you can tell a graph to plot the Sum of the Data Series as its data plot. Is there a similar option in Excel. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Graphing the sum of an autofilter result
Terminology: A series is not a single point. My suggestion would result in a
series with two points. You can either set up a table with your group names in the first column, and formulas in the second column that added the appropriate values. Say your data is in A2:B100. In D2 start listing the group names. In E2 enter this formula: =SUMIF($A$2:$A$100,D2,$B$2:$B$100) This gives a sum for all of the groups. This table would be similar to the pivot table. Is your intention to chart a single point? With nothing to compare it to? Seems incomplete to me, but here's some clues. Add a dropdown or listbox from the Forms toolbar. Set its Input Range to the list started in D2, and set its Cell Link to G2. In H2 enter this formula =INDEX(D2:D10,$G$2) Substitute your actual last cell for D10. H2 will show the item selected in the dropdown or listbox. Copy H2, select I2 and paste. I2 will now contain the sum for the item selected in the dropdown or listbox. Make your chart using the cells H2 and I2. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "ElPresidente" wrote in message ... In my first example, if I filtered by Group 1, I would want the graph to combine the two rows in a plot showing $40, not two series showing $20 each. I know this can be accomplished using PivotCharts, but unless I'm wrong, the chart has to be linked to the pivot table. If I have 30 different graphs, it would require 30 pivot tables? In Access, you can tell a graph to plot the Sum of the Data Series as its data plot. Is there a similar option in Excel. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I stop excell graphing no data as a 'zero' result? | Charts and Charting in Excel | |||
autofilter none result | Excel Programming | |||
Macro to select the result of an autofilter action | Excel Programming | |||
referencing autofilter result | Excel Discussion (Misc queries) | |||
Using the result of autofilter and performing tasks on each value | Excel Programming |