Dynamic line Chart


I'm working on a simple dashboard that will contain highlights of information for baseball teams.  I've successfully figured out how to change the picture dynamically through named ranges and Picture Links.

Now what I'd like to do is include a line chart on the dashboard page showing the total data points (could be anything- sales, attendance, revenues, popularity rating) for each month.  The trick is not all of the teams will have information for every month and/or may not stop in Jan. and end in Dec.

I'm wondering if there is a way to dynamically create the chart so that it picks the correct range on the x and y axes and shows the data points for those month/data point intersections.

Any body know how to do that if it's possible?
BBluAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

this would be a lot easier to solve if you provided some clue about your data layout. Where is the source data, what are the possible variances in the ranges, how is the data arranged (i.e. in columns or rows), etc, etc.

With the information you have provided above, the correct answer is: yes, it's possible.

You can create a chart based on a dynamic range that will include only populated cells from a specific row/column in a given table and show the appropriate X axis labels.

If you need anything more specific than that, you'll have to provide the framework to work with.

cheers, teylyn
0
dlmilleCommented:
Here are three recent questions that I participated in where dynamic ranges were the topic of discussion, resulting in dynamic charts.  While not tutorials, if you're a - look and learn - type of person, perhaps this is what you need:

http://www.experts-exchange.com/Software/Internet_Email/Web_Browsers/Q_26900822.html
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26867086.html
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26839152.html

Let me know if you have particular questions.  If you submit structure, then looks like teylyn has teed up to assist.

Cheers,

Dave
0
BBluAuthor Commented:
Sorry, guys.  I thought I'd included the spreadsheet.  Here it is. MLB-Dashboard-a.xlsx
0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

One option is to create a pivot chart in the dashboard. The data for the pivot chart is underneath the actual chart. Use the filter in the chart to select the team.

Or, you can hide the filter selection button in the chart and use the filter in the pivot table. Drag the chart into the desired position to hide/show the pivot table filter.

If you don't want to use a pivot table, you will need to create a summary table somewhere else in the sheet to base your chart on.

A chart (other than a pivot chart) will not automatically summarise your data. You need to provide that. The Pivot Chart is the fastest way to achieve that with just a few clicks.

See attached.

cheers, teylyn
MLB-Dashboard-a.xlsx
0
BBluAuthor Commented:
Thanks, Teylyn.  Is there a way to still allow the user to change the team, which will change the filter and the image that shows?
0
BBluAuthor Commented:
I just had an idea, but am not sure if it's possible or how exactly to write it.  What if I created a hidden table somewhere. The first value row of that table would use the large function on the whole Date column, but only include items where the item in the Team Column is, say, the Yankees.  I think there is an array formula or something that could accomplish that.  

Then after that, following the lead from Dave, I could use a combination of the Counta and Offset Functions to determine how far down the columns the graph should reference for it's data.

Does that make sense?
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
BBlue, in the file I attached you can change the filter to another team and the icon will adjust. You can either use the in-chart button to do that, or use the pivot table filter.

The pivot table is really the easiest way to summarise your data. I'll have a look into another suggestion.

cheers, teylyn
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
One question for clarification: If a team has values for Jan, Feb and April, but not for March, what do you want to see on the X axis? Four months and the line will connect the Feb and April values? That would be best practice. In a line chart, you don't normally omit months just because they don't have data. You either drop the line to 0 or leave a gap for the missing point or connect the points before and after the missing point.

Any thoughts?
0
BBluAuthor Commented:
But the pivot table is hidden (because it makes the dashboard cleaner).  So the user would have to move the chart, first, then change the filter.
0
BBluAuthor Commented:
I think I'm going to try to write a macro that creates an array of all of the items that have the same team as the one in the cell containing the team name.  It then will use the Excel large function to return the nth largest value.  I don't know if it'll work, but now I feel challenged.  LOL.
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
See attached.

the chart is set to connect data points with lines. Leaving gaps is not easily achieved when the source data is calculated with a formula (it can be done, but it requires a more complex setup). If you want the line to drop to zero, you need to change the source formula to show 0 instead of N/A.

Let me know if that is closer to what you want to achieve.

cheers, teylyn
Copy-of-MLB-Dashboard-a.xlsx
0
dlmilleCommented:
What exactly are you trying to plot?  See attached with teylyn's pivot approach.  PS - click on the chart to see the settings and you can change months, teams, etc...

Dave
MLB-Dashboard-a-r1.xlsx
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
BBLue, can you please answer my question above: If a team has values for Jan, Feb and April, but not for March, what do you want to see on the X axis?

Do you want to omit the March altogether and have just Jan, Feb, April on the chart? That would be very bad charting practice. If you show a chart with months on the X axis, the reader will expect the third month to be the month after the second month.

Please explain what you want to achieve.
0
BBluAuthor Commented:
I think you're right.  We probably should show the absent months either way, but dropping to 0.  I hadn't thought about that actually.  What you provide above (35266409) works, though.  That's what I was looking for, except I guess for 0 instead of N/A's
0
BBluAuthor Commented:
Thanks to you, too, Dave.  But I don't have the most savvy of users, so I'd prefer to not have them click on the chart and have to make changes that way.
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
See attached. The values drop to zero. But for this scenario, a line chart is the wrong chart type. I would use a column chart instead, which will show the gaps much better, i.e. months not played. The dropping line creates the (wrong) impression of a decreasing value, which is misleading.

cheers, teylyn
Copy-of-MLB-Dashboard-a.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BBluAuthor Commented:
Thank you both very much!  Thanks tylyn for making several attempts at what I was trying to get across.  We finally got there.  Sorry for the confusion.  Dave, thanks for your help, too.  That link for dynamic charting areas (using countA and offset functions) was also something I can keep in my back pocket for the future.  Have a wonderful day/evening both of you.
0
BBluAuthor Commented:
One quick follow-up question, teylyn.  What do the double-dashes mean in your sumproduct formulas:

i.e.
SUMPRODUCT(--($A$1:$A$64=J$3),--($C$1:$C$64=$F4),$B$1:$B$64)
0
dlmilleCommented:
No worries and you were very generous with the points - teylyn did all the work :)

When you get around to doing the dynamic range stuff, don't hesitate to post - ask a related question to this or the other links I gave you and I / others will be quick to respond.

Cheers,

Dave
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
BBlu, thanks for the grade.

The double dashes convert the True/False results inside the brackets to 1/0 numbers, which can then be used in calculations.  See http://www.xldynamic.com/source/xld.SUMPRODUCT.html#format
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.