Hi everyone.
I'm trying to produce a clustered and stacked (or stacked and clustered depending on your phrasing) bar chart. The rows in my data can have multiple values spanning across multiple columns, but not all row/column intersections will have values (and at certain times, rows will have 0 values in them.)
For sake of this example, I've changed all of the 0 values in my data to N/A - this can be changed back if necessary (I googled a simple formula for converting 0 to N/A). Columns/row labels have also been changed, and the A/B/C/D for clustering needs to happen (names also changed). Odds are I will have to reorganize the data manually into said clusters, as Rows 14/15 are a part of cluster A (but I can fix that manually).
What I'm trying to do is to produce the clustered AND stacked bar chart AND NOT include these 0 or N/A values in the data because I have 28 columns/24 rows of data, which will make the bar chart filled with blanks. These row/column intersection values will change monthly, as values can change and become 0, or 0 values can become non-zero depending on the data obtained for the month. Entire rows and/or column may be filled with zeroes (rarely), but this can also change monthly.
Screenshot 1 (shows my data, and all of the N/A values which are 0's): imgur: the simple image sharer
Screenshot 2 (shows the current version of my S/C bar chart - notice all of the blanks in the clusters, these are what I'm trying to get rid of, to make the chart smaller and more presentable): imgur: the simple image sharer
I am using Excel 2010 - and I do not have a concrete understanding of Excel (I've learned bits and pieces).
Perhaps it may be more feasible to create a VLookup to build another table off of this data, and make a S/C bar chart based on that?
Any guidance/tips/help that can be provided would be appreciated. Many thanks in advance.
I'm trying to produce a clustered and stacked (or stacked and clustered depending on your phrasing) bar chart. The rows in my data can have multiple values spanning across multiple columns, but not all row/column intersections will have values (and at certain times, rows will have 0 values in them.)
For sake of this example, I've changed all of the 0 values in my data to N/A - this can be changed back if necessary (I googled a simple formula for converting 0 to N/A). Columns/row labels have also been changed, and the A/B/C/D for clustering needs to happen (names also changed). Odds are I will have to reorganize the data manually into said clusters, as Rows 14/15 are a part of cluster A (but I can fix that manually).
What I'm trying to do is to produce the clustered AND stacked bar chart AND NOT include these 0 or N/A values in the data because I have 28 columns/24 rows of data, which will make the bar chart filled with blanks. These row/column intersection values will change monthly, as values can change and become 0, or 0 values can become non-zero depending on the data obtained for the month. Entire rows and/or column may be filled with zeroes (rarely), but this can also change monthly.
Screenshot 1 (shows my data, and all of the N/A values which are 0's): imgur: the simple image sharer
Screenshot 2 (shows the current version of my S/C bar chart - notice all of the blanks in the clusters, these are what I'm trying to get rid of, to make the chart smaller and more presentable): imgur: the simple image sharer
I am using Excel 2010 - and I do not have a concrete understanding of Excel (I've learned bits and pieces).
Perhaps it may be more feasible to create a VLookup to build another table off of this data, and make a S/C bar chart based on that?
Any guidance/tips/help that can be provided would be appreciated. Many thanks in advance.
