Results 1 to 4 of 4
  1. #1
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Combo chart (Stacked Bar and Regular Bar) (2000)

    How do you want the chart setup?
    How many series? Here is 1 scenario:
    Series 1 (color1) unstacked
    Series 2 (color2) unstacked
    Series 3 (color3) stacked bottom
    Series 4 (color4) stacked top

    Though you could also have:
    Series 1 (color1) unstacked
    Series 2 (color1) unstacked
    Series 3 (color1) stacked bottom
    Series 4 (color2) stacked top

    Or even:
    Series 1 (color1) unstacked
    Series 2 (color1) unstacked
    Series 3 (color2) stacked bottom
    Series 4 (color3) stacked top

    Or even:
    Series 1 (color1) unstacked
    Series 2 (color2) unstacked
    Series 3 (color1) stacked bottom
    Series 4 (color3) stacked top
    Etc, etc

    Do you have a small dataset? It might be easier to demo. You essentially have a column for each "color" make them all "stacked". The ones that don't look like they are stacked, have zeroes in all the columns except for the one column that plots. The stacked portion will use the difference in the ranges (stacks ADD the values together)


    Steve

  2. #2
    New Lounger
    Join Date
    Jan 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo chart (Stacked Bar and Regular Bar) (2000)

    How do I make a combo column chart that stacks 2 of the 4 data series and leaves the other 2 series as a regular column?

    The data I want to plot is below. I want to have 3 bars total. FY01, FY02 and to Stack FY03 Web and Phone.

    <table border=1><tr><td align=right>

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Combo chart (Stacked Bar and Regular Bar) (2000)

    You could also plot the the final two series on the secondary axis. Assign the stacked bar chart type to them and fool around with the 'gap' between bars, until the positioning satisfies you.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Combo chart (Stacked Bar and Regular Bar) (2000)

    How does this work?
    Created a stacked bar with 4 each of the series as a separate item. The key points:
    Each Quarter has 3 sections:
    Section 1: For Series 1 (FY01)only (with nothing to stack, it is alone)
    Section 2: For Series 2 (FY02) only (with nothing to stack, it is alone)
    Section 3: For Series 3 and 4 (FY03s) (with only these 2 they will stack)

    I used an index and match equation to "extract" the info from your data into more "graph- friendly" form:

    <pre>=IF(B$7=LEFT($A10,4),INDEX(DataTable,MATCH($A 10,FYList,FALSE),MATCH(B$8,QList,FALSE)),0)</pre>


    DataTable is the data, QList the range of the 4 Qs, and FYList the range of 4 FYs.
    My transformed graph table looks like this:

    <table border=1><td valign=bottom> </td><td valign=bottom>FY01</td><td valign=bottom>FY02</td><td valign=bottom>FY03</td><td valign=bottom>FY01</td><td valign=bottom>FY02</td><td valign=bottom>FY03</td><td valign=bottom>FY01</td><td valign=bottom>FY02</td><td valign=bottom>FY03</td><td valign=bottom>FY01</td><td valign=bottom>FY02</td><td valign=bottom>FY03</td><td valign=bottom> </td><td valign=bottom>Q1</td><td valign=bottom>Q1</td><td valign=bottom>Q1</td><td valign=bottom>Q2</td><td valign=bottom>Q2</td><td valign=bottom>Q2</td><td valign=bottom>Q3</td><td valign=bottom>Q3</td><td valign=bottom>Q3</td><td valign=bottom>Q4</td><td valign=bottom>Q4</td><td valign=bottom>Q4</td><td valign=bottom> </td><td valign=bottom> </td><td valign=bottom>Q1</td><td valign=bottom> </td><td valign=bottom> </td><td valign=bottom>Q2</td><td valign=bottom> </td><td valign=bottom> </td><td valign=bottom>Q3</td><td valign=bottom> </td><td valign=bottom> </td><td valign=bottom>Q4</td><td valign=bottom> </td><td valign=bottom>FY01</td><td align=right valign=bottom>50</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>85</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>45</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>89</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td valign=bottom>FY02</td><td align=right valign=bottom>0</td><td align=right valign=bottom>84</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>44</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>17</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>87</td><td align=right valign=bottom>0</td><td valign=bottom>FY03 Phone</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>25</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>78</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>52</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>45</td><td valign=bottom>FY03 Web</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>49</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>54</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>54</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>48</td></table>


    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •