Results 1 to 10 of 10
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trend Line on Stacked Chart (Excel97SR2 et al.)

    Using a stacked chart, I can't add a trend line.

    I know why I can't: it's because MS doesn't allow it.

    I can't understand why it ought not to be possible.

    For example, If I've produced a stacked bar chart with three columns of data (tea money, coffee money, biscuit money) and want to show my total office expenses, a stacked chart makes sense. The top of each bar represents a scalar amount - the total spent on consumables for each month.

    It seems to me that it ought not to be a problem dropping a trend line onto a stacked column chart.

    Clearly (?!!) I can enter formulae in the worksheet that deliver the total consumables for each month, and I can use the LineEst, Growth, or other functions to lay out data. I can chart this sub-total and add a trendline to that chart.

    I just can't find a logical reason for not allowing a trendline to be added to a stacked-column chart.

    I don't think I'm asking the impossible (for example, a trend line to a Pie Chart!).

    Any comments? Especially welcome are notes on faults in my thinking process.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trend Line on Stacked Chart (Excel97SR2 et al.)

    If you look at the Excel object model in the Visual Basic Editor, you will see that the Trendlines collection is a child of the SeriesCollection collection. Each trend line belongs to a specific series in the chart. The trend line you want does not belong to a single series, but to the sum of all series. The object model does not provide for that, it would have to be changed to allow for a 'totals" trend line.

  3. #3
    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: Trend Line on Stacked Chart (Excel97SR2 et al.)

    The easiest thing I can imagine is to create a dataset with the sum of the columns and add this to the chart as another series. Make its chart type a line. The add a trendline for these points. Then format the line and markers to be none for this series so only the trendline is there.

    This way doesn't involve the LINEST, GROWTH, TREND, etc. it only requires 1 additional range that is calculated: the sum for each category.

    Steve

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trend Line on Stacked Chart (Excel97SR2 et al.

    "I know why I can't: it's because MS doesn't allow it. I can't understand why it ought not to be possible."

    > it would have to be changed to allow for a 'totals" trend line.

    Hans, thanks for the response. I *think* that you are agreeing with me: It's not available because MS didn't make it available; there's no logical reason why it couldn't be available?

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trend Line on Stacked Chart (Excel97SR2 et al.

    > easiest thing I can imagine is to create a dataset

    Steve, thanks. In effect you are saying that it makes sense to be able to have a trendline for a stacked column? We kmnow that MS doesn't provide it, but can you think of a reason it shouldn't be available?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trend Line on Stacked Chart (Excel97SR2 et al.

    Of course, a "totals" trend line would have been possible; I tried to point out that it doesn't fit into the current object model, so it wouldn't be a simple matter of adding it. It would require a fundamental change of the way trend lines fit into the object model for charts.

    As Steve pointed out, you CAN create a totals trend line by using a trick: calculate the totals, add them as a hidden line series to the chart, and create a trend line for this series. See attached example.

  7. #7
    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: Trend Line on Stacked Chart (Excel97SR2 et al.

    Thank you for creating the example. This is exactly what I had in mind in my original response.

    Steve

  8. #8
    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: Trend Line on Stacked Chart (Excel97SR2 et al.

    I suppose it could have been done by MS in some way.

    but in some sense, you can do it "indirectly", as I mentioned and Hans demonstrated.

    Remember you are giving excel multiple values. If you were to create a trendline for any of your ranges, only the first range would be "correct" (related to the top of the column) the others would be too low. You are looking for a trend of a combination of all the ranges, and while it could be done, I am not sure that is how excel works and uses these numbers. I do not think it has anywhere the "SUM" of the categories.

    Excel can not be flexible enough to have builtin routines to do everything that everyone wants/needs. It does have a flexible enough routines that if given the sums (in a separate range) and you plot them, you can create the trendline.

    Steve

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trend Line on Stacked Chart (Excel97SR2 et al.

    > You are looking for a trend of a combination of all the ranges

    The sum, actually, and since the top of the stacked column represents the sum, it made sense to me to be able to present a trend line for the sum of the component values. Quite obviously one can use the DRAW toolbar to place a straight-line trend line running along the top of the stacked columns. I could find no logical reason for not allowing that with the right-click.


    >Excel can not be flexible enough to have builtin routines to do everything that everyone wants/needs

    Quite so. But i wa struggling to come up with a reason for it not being POSSIBLE. As earlier response have (I hope!) indicated, my reasoning is sound - a trrend line of the sum makes sense. it just isn't implemented.

  10. #10
    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: Trend Line on Stacked Chart (Excel97SR2 et al.

    But it is possible.

    I told how to do it and Hans even demonstrated it for you in a file.

    This should work exactly as you imagine, it is live, will change as the chart data changes. In terms of a "workaround" it barely qualifies: i would say it is more a direct method than most of workarounds required for things that are barely possible.

    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
  •