Excel Dynamic Chart #16: Dynamic Average Hurdle Line for Bar Chart. Scatter and Bar Chart Together.

Howdy All!

I've been working on a project in Access. It works fine, and exports tables to Excel. The number of records varies each time the Access file exports. Therefore, I've created a chart w/ dynamic ranges.

The chart works fine....It grows and shrinks accordingly. I'd like to include a line to show the average...dynamically. I found an equation that works w/ dynamic ranges. This function works well (found in cell C2).

However, I can't get that info turned into a line on my column chart. When I add that value into the chart, and change it to a line chart, I end up w/ one single data point. Understandably, because that's all there is.

Therein lies the question: How do I apply that value dynamically for each record so it shows as a line on my chart?

I've tried using trend lines, but they don't actually average...so they're not very helpful.

Attached is an example. Feel free to play w/ the data (add or remove as you will). The column chart changes accordingly, as does the average value in C2. But...I'd still like to get the average value to show as a line...

Thanks for the help.

I'm well and truely stuck on this one and was wondering if anyone had any ideas.

I have three tables. Each one contains data i use as a separate data series on a scatter graph. (I have them set up dynamically so they expand and everything works nicely.)

The problem I have is if one of the tables is blank with no records in it. It screws up my chart. It shows on the chart as a maker equal to zero and returns a message "A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a valid path, workbook, named range, and cell reference."

Any ideas how go about setting up the scatter graph so that it will ignore blank data series ?

Hi there, I've googled for days on how to make a dynamic chart with sum/average of multiple columns. For example, data sample Col1,Col2,Col3 1,10,100 2,20,200 3,30,300 The XY scatter chart is X-axis from Col1 and sum/average of Col2 and Col3 using named_range. My real data have hundreds of rows and about hundred columns. I found many examples of making interactive charts, selected dataset or moving average of the column. The named_range "=AVERAGE(OFFSET($A$1,0,1,,2)" does not produced dynamic/moving result. Any idea? Thanks.

Update: Xlsx sample attached.

I have a line chart, dates on X axis and Numbers on Y and I layered in a scatter chart showing important events on certain dates. So the dates on the X axis are in months and years like Mar-01 and they are between 2000 and 2006. I only have 20 date points between those, but the chart shows every month between 2000 and 2006 but I dont have data for every month, just certain months. How can I fix this?

I'm trying to connect point on a (background graphic) circle with lines
(using Excel 2000). I have generated the X-cordinates for all ends of lines
in one column and, in each of the following columns, I have the Y-cordinates
for one line, two values next to each other; in the same rows as their
X-coordinates. All other cells are empty.

This works in smaller scale but when I have 250 lines, the X-values are
replaced with rising numbers, i.e. I get a line graphic.

Is there any way to make the scatter graphic work in large scale figures?

Thanks

I have designed a couple of graphs using named ranges to give dynamics to the number of series in a chart. This works fine as long as I don't want to copy and/or move the sheet to a new work book. When I do this, Excel chooses to remove all references to the named ranges in the SERIES forumla and replace them with fixed references (despite having scoped all named ranges to WS scope).

From googling I found the tip to save the WB as a template and use Insert sheet to add the graphs. This does not quite work as expected though. The SERIES formula points to the right named ranges, but when I update data the graph is not updated. If I save the new workbook and reopen it, it works as expected. Other quirks include not being able to Insert a second template without saving and reopening (the named ranges points to wrong sheet if I do not do this).

So my question is two-fold; has anyone seen this problem before and solved it? Or, has anyone been able to successfully copy/move a sheet and keep the named ranges without involving a "relink" macro?

I've searched the forums and have been unable to accomplish my tasks based on what I've found.

So I'm trying to create a dynamic line chart that updates every month. Attached is a sample workbook. In the example, I want to be able to add the month of September along with its sals and profit, and have the chart update automatically.

I've seen some examples using the offset function, but I can't seem to get it to work.

Thanks in advance!

Writing a macro to determine the number of rows that need to be graphed and then creating the graph. This number will change depending on previous macros. Code is below. I keep receiving an error message when trying to run stating "Run Time Erro '1004': Method 'Cells' of object '_Global' failed."

Sub WAPChart()

Dim Counter As Long
Dim RID As Byte

Counter = Application.CountA(Sheets("UserInput").Range("AX2:AX25"))
RID = Counter + 1

Charts.Add
ActiveChart.ChartType = xlLineMarkers
With ActiveChart
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
End With
With ActiveChart.SeriesCollection.NewSeries
.Name = "Experimental"
.Values = Sheets("UserInput").Range(Cells(2, 50), Cells(RID, 50))
.XValues = Sheets("UserInput").Range(Cells(2, 52), Cells(RID, 52))
End With
With ActiveChart.SeriesCollection.NewSeries
.Name = "Adjusted Nominal"
.Values = Sheets("UserInput").Range(Cells(2, 51), Cells(RID, 51))
.XValues = Sheets("UserInput").Range(Cells(2, 52), Cells(RID, 52))
End With

ActiveChart.Location Whe =xlLocationAsObject, Name:="Charts"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlRight

End Sub

Pardon if this has been asked already in one form or another.

I have multiple line charts that I use to trend a top ten scoring system over time. The top ten is dynamic and is averaged out of 60 possible items and changes frequently. Each of the 60 items has a static color assigned to it. Naturlly the chart colors stay static as well. Is there a way to make the chart colors/legion dynamic and have it update the colors as the dynamic list is updated?

Thanks
BC