# Trend line on a stacked bar diagram.

1. ## 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:
that shows the average of the bottom bar to date.

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

2. ## 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?

3. ## Re: Trend line on a stacked bar diagram.

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

4. ## 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. ## Re: Trend line on a stacked bar diagram.

You can not do trend lines on stacked columns.

6. ## 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. ## 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?

8. ## 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

9. ## 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. ## 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.

11. ## 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

12. ## 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. ## 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.

14. ## 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.

15. ## 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."

16. ## Re: Trend line on a stacked bar diagram.

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

17. ## 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. ## Re: Trend line on a stacked bar diagram.

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

19. ## 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. ## 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. ## 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. ## 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. ## Re: Trend line on a stacked bar diagram.

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

24. ## 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.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

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. ## 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. ## Re: Trend line on a stacked bar diagram.

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

27. ## 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. ## Re: Trend line on a stacked bar diagram.

Originally Posted by itr674
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???

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

29. ## Re: Trend line on a stacked bar diagram.

Copy all, I had a weak moment...

##### Users Browsing this Thread

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

#### Posting Permissions

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