+ Reply to Thread
Results 1 to 29 of 29

Trend line on a stacked bar diagram.

  1. #1
    Registered User
    Join Date
    07-03-2013
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    89

    Trend line on a stacked bar diagram.

    Hi,
    i have a stacked bar chart, similar to this:
    http://www.roseindia.net/chartgraphs...BarChart3D.gif
    What i need is a trend line, similar to this:
    http://support.sas.com/kb/35/addl/fu...dout_trend.gif
    that shows the average of the bottom bar to date.

    is this doable in excel??
    appreciate any help, or solutions to ismilar problems

  2. #2
    Registered User
    Join Date
    07-10-2014
    Location
    East Sussex, England
    MS-Off Ver
    2007, 2010
    Posts
    51

    Re: Trend line on a stacked bar diagram.

    I once did something like this to get a trend line over another chart.

    Create two charts exactly the same size, that both read from the same data. Then overlay one chart exactly on the other, and format all the visible bits to white text and remove the background colour of the top chart and the second one shows through...

    Could you do this in your situation?
    Robbo

  3. #3
    Registered User
    Join Date
    07-03-2013
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Trend line on a stacked bar diagram.

    If it works, i will give it a shot =) thanks for the suggestion!

  4. #4
    Registered User
    Join Date
    07-03-2013
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Trend line on a stacked bar diagram.

    Actually on second thought i dont think it will work. if the value of one of the bars other than the one im taking the avg. of become bigger than the max value currently showing, the first chart will scale, whilst the avg. chart won't scale the same, and they will mismatch.
    The chart is dynamic, so I think i will need another solution. still thanks for the suggestion!

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,650

    Re: Trend line on a stacked bar diagram.

    You can not do trend lines on stacked columns.
    Cheers
    Andy
    www.andypope.info

  6. #6
    Registered User
    Join Date
    07-03-2013
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Trend line on a stacked bar diagram.

    ok, is there a way to use what Rob Kennedy suggested, but ensure that the max y value is the same for both charts, so they scale the same?

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,650

    Re: Trend line on a stacked bar diagram.

    It would only work for the bottom layer. but if it is only that layer you want you can do it by creating a dummy series on the secondary axis and adding trendline to that.

    The reason why trend lines are not allowed is the base line for the remaining series is not level.

    Take a look at the attached. Top left chart is a cluster column with trend lines. Because they are centered it would be better using a Line chart, which is top right example.

    Now try putting those onto a stacked column by using the secondary axis and plotting lines based on trendline information (in this case manually calculated). See how meaningless the lines are?
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-03-2013
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Trend line on a stacked bar diagram.

    I just need it on the bottom one, and the trend the avg. of the blue in this case. This would be very useful for my purpose, and in no way meaningless
    However when i have smaller values in 1 it scales so it looks misleading. Is there a way to have both y-axis have the same max value?
    I changed the file you sent me to represent what i need
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-03-2013
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Trend line on a stacked bar diagram.

    I know i can manually set it to be 8, but i want it to scale after the primary one. is there in worst case a code that can change it to be equal ?

  10. #10
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,650

    Re: Trend line on a stacked bar diagram.

    Add data to secondary axis and then delete secondary value axis. This will force the series on the secondary axis to use the same scaling as the primary.

    I have added 2 series to secondary axis. One repeats X's data the other is average of XYZ.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    07-03-2013
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Trend line on a stacked bar diagram.

    I think that'll work well Can i just ask one more thing, the avg i need is not the avg of xyz, or just the x value itself, its the avg of X value to date. so first value should be 1, then 1.5, followed by 2.0. Is there a simple way to refer to the avg. to date using a named range or something like that?

    edit: also my range is dynamic, new data will be added continuously, so it will need to be able to update the line based on new data being added in the column.

    It is what i am asking for here btw, if i should mark this threead as solved.
    http://www.excelforum.com/excel-gene...mic-range.html
    Last edited by siggisigg; 07-15-2014 at 08:31 AM.

  12. #12
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,650

    Re: Trend line on a stacked bar diagram.

    So you are actually after a datum line rather than a trend line? By that I mean a Line series plotting your values rather than a series with Add TrendLine applied.

    =AVERAGE(NAMED_RANGE)

  13. #13
    Registered User
    Join Date
    07-03-2013
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Trend line on a stacked bar diagram.

    i guess a line series is what i meant, yes. However i dont need it to be a straight line showing the avg value of the data set. each point is the avg. up to that date. so first point will be the avg of the first point. second point along the line will be the avg. of the first and second value. and so on.

    in other words the last value should have no impact on the start of the line. the line showing the avg to date should just move along the stacked columns showing how the avg. to that date is changing with each new date it take into account.

    Its starting to get late in the day, and my explaining is probably becoming very hard to interpret, but bear with me

    edit: Currently im using a dynamic named range to continuously update the stacked bar charts, which goes as follows: =OFFSET(Data!$J$2;;;COUNTA(Data!$J:$J)-1
    However i have no idea how to do what i need above.
    Last edited by siggisigg; 07-15-2014 at 08:45 AM.

  14. #14
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,650

    Re: Trend line on a stacked bar diagram.

    See attached. The chart is based on formula calculating average.

    But it also includes named range example which you could use.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    07-03-2013
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Trend line on a stacked bar diagram.

    This is exactly what i'm looking for! thank you so much. I'm not sure how to use the named ranga nd actually make it only take the avg up to the point where its at. as its now it will jus tupdate the avg for all the points to be the same if i use average on it. however i can just make an additional column like you did, and use a macro to fill it in as i import data.


    tried to give you rep. for helping me out, but you already helped me too much today it seems:P
    "You must spread some Reputation around before giving it to Andy Pope again."
    Last edited by siggisigg; 07-15-2014 at 08:59 AM.

  16. #16
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,650

    Re: Trend line on a stacked bar diagram.

    check you calculation mode. It may be manually rather than automatiic

  17. #17
    Registered User
    Join Date
    07-03-2013
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Trend line on a stacked bar diagram.

    now however when i need this macro to fill out, what in the world would be the code? it seemed simple when i started thinking about it, but when i tried it proved rather challenging.
    the data im taking the avg of is in J2:Jx, where x will increase. ill put the avg value in the AA value in the same row.
    so in AA2, the value will be the same as J2
    in AA3 the value will be (J2+J3)/2
    in AA(x) the value is (J2+..+J(x))/(x-1)

    my macro that i want to implement this in will always fill out one row of information, im planning on having this code at the end of it, so it will fill out the avg up to this specidifc point.
    any ideas?

  18. #18
    Registered User
    Join Date
    07-03-2013
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Trend line on a stacked bar diagram.

    HTML Code: 
    'wb.Sheets("Data").Range("AA" & Rows.Count).End(xlUp).Offset(1) = ??

  19. #19
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,650

    Re: Trend line on a stacked bar diagram.

    This uses data in column J and outputs average values in column M

    Please Login or Register  to view this content.
    Or this if you also need to calculate number of cells, rather than static range.
    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    07-03-2013
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Trend line on a stacked bar diagram.

    i dont really understand the code very well. does it always asign the value to the cell M2? i want it to have one value in M2, and then the next value in M3, so it has to be able to count which row is the first empty one. or does it do that without me seeing how? im not exactly sure what the resize function does, so i might just be wrong.

  21. #21
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,650

    Re: Trend line on a stacked bar diagram.

    M2 is the starting point. The range is the resized to the correct number of rows and the formula is then applied to that range.

  22. #22
    Registered User
    Join Date
    07-03-2013
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Trend line on a stacked bar diagram.

    it keeps giving me "application-defined or object-defined error", im not sure why. It is in the first part of the code is all i know.

  23. #23
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,650

    Re: Trend line on a stacked bar diagram.

    post what you are currently using so we can see the actual problem

  24. #24
    Registered User
    Join Date
    07-03-2013
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Trend line on a stacked bar diagram.

    the entire code, or a sample? I'll start making a sample, meanwhile i'll upload the code im using.


    HTML Code: 
    Sub Button1_Click()
    
     Dim wb As Workbook
     Dim wb2 As Workbook
     Dim vFile As Variant
     Dim wS As Worksheet
     Dim lRow As Long, lCol As Long
     Dim oC As Range
     
     Application.EnableEvents = False
     Application.ScreenUpdating = False
     Application.DisplayAlerts = False
     Application.Calculation = xlManual
     Set wb = ThisWorkbook
     vFile = Application.GetOpenFilename("Excel-files,*.xlsx", _
     1, "Select One File To Open", , False)
     If TypeName(vFile) = "Boolean" Then Exit Sub
     Set wb2 = Workbooks.Open(vFile)
     
    wb.Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(2).Range("E3").Value   
    
    wb.Sheets("Data").Range("E" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(1).Range("N52").Value  
    wb.Sheets("Data").Range("F" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(1).Range("N51").Value  
    
    wb.Sheets("Data").Range("G" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(2).Range("E23").Value   
    wb.Sheets("Data").Range("H" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(2).Range("E24").Value   
    wb.Sheets("Data").Range("I" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(2).Range("E25").Value   
    
    wb.Sheets("Data").Range("J" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(2).Range("F23").Value 
    wb.Sheets("Data").Range("K" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(2).Range("F24").Value 
    wb.Sheets("Data").Range("L" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(2).Range("F25").Value 
    
    wb.Sheets("Data").Range("M" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(2).Range("E19").Value 
    wb.Sheets("Data").Range("N" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(2).Range("F19").Value 
    wb.Sheets("Data").Range("O" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(2).Range("G19").Value 
    
    wb.Sheets("Data").Range("P" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(2).Range("E10").Value 
    wb.Sheets("Data").Range("Q" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(2).Range("E11").Value 
    wb.Sheets("Data").Range("R" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(2).Range("E12").Value 
    
    wb.Sheets("Data").Range("S" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(1).Range("W51").Value 
    wb.Sheets("Data").Range("T" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(1).Range("X51").Value 
    wb.Sheets("Data").Range("U" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(1).Range("Y51").Value 
    
    wb.Sheets("Data").Range("V" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(2).Range("G16").Value 
    wb.Sheets("Data").Range("W" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(2).Range("G17").Value 
    wb.Sheets("Data").Range("X" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(2).Range("G18").Value 
    
    wb.Sheets("Data").Range("Y" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(1).Range("Y48").Value 
    
    wb.Sheets("Data").Range("Z" & Rows.Count).End(xlUp).Offset(1).FormulaR1C1 = "=Left(RC[-25],2)"
    wb.Sheets("Data").Range("B" & Rows.Count).End(xlUp).Offset(1).FormulaR1C1 = "=IF(RC[24]=""01"",""Q1"",(IF(RC[24]=""04"",""Q2"",IF(RC[24]=""07"",""Q3"",IF(RC[24]=""10"",""Q4"",NA())))))"
    
    wb.Sheets("Data").Range("C" & Rows.Count).End(xlUp).Offset(1).FormulaR1C1 = "=MID(RC[-2],6,2)" 
    wb.Sheets("Data").Range("D" & Rows.Count).End(xlUp).Offset(1).FormulaR1C1 = "=CONCATENATE(RC[-2],"" "",RC[-1])" 
    
    Range("AA2").Resize(Application.WorksheetFunction.Count(Range("J:J")) - 1, 1).FormulaR1C1 = "=AVERAGE(R2C10:RC[-3])"
    
    
    
      wb2.Close
        
    
        'Set all empty cells = 0:
        Set wS = Worksheets("Data")
        lRow = wS.Cells(wS.Rows.Count, 1).End(xlUp).Row
        lCol = wS.Cells(1, wS.Columns.Count).End(xlToLeft).Column
        For Each oC In wS.Range(wS.Cells(lRow, 1), wS.Cells(lRow, lCol))
            If oC.Value = "" Then oC.Value = 0
        Next oC
    
    
     Application.EnableEvents = True
     Application.ScreenUpdating = True
     Application.DisplayAlerts = True
    
    End Sub
    edit: I have reformulated the question here
    http://www.excelforum.com/excel-prog...ml#post3769052
    in case the thread is in a more relevant forum there.

  25. #25
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,650

    Re: Trend line on a stacked bar diagram.

    Use the other thread.
    Does Olly's suggestion work?

    You need to post working workbook. Code alone is not enough, we need to see data. More specifically it's the data layout but the dummy data you provide must still allow the problem to occur.

  26. #26
    Registered User
    Join Date
    07-03-2013
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Trend line on a stacked bar diagram.

    uploaded sample to other thread. his code is making my WB crash.

  27. #27
    Registered User
    Join Date
    10-05-2016
    Location
    NC
    MS-Off Ver
    2016
    Posts
    5

    Re: Trend line on a stacked bar diagram.

    I have been trying to use this method on a "stacked bar" not stacked column and I can't get it to work, other that using the XY scatter method???

  28. #28
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,177

    Re: Trend line on a stacked bar diagram.

    Quote Originally Posted by itr674 View Post
    I have been trying to use this method on a "stacked bar" not stacked column and I can't get it to work, other that using the XY scatter method???
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  29. #29
    Registered User
    Join Date
    10-05-2016
    Location
    NC
    MS-Off Ver
    2016
    Posts
    5

    Re: Trend line on a stacked bar diagram.

    Copy all, I had a weak moment...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 01-28-2013, 09:01 AM
  2. Difficulty adding a trend line to line graph in Excel 2007
    By jmatoske in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 11-28-2012, 02:59 PM
  3. Replies: 2
    Last Post: 11-18-2012, 11:20 AM
  4. to create a chart/ trend diagram from data in two excel sheets.
    By anishmathw in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 07-19-2012, 10:56 AM
  5. How do I add a trend line to a stacked chart in Excel
    By Marea in Canberra in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-10-2006, 04:00 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1