Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Dynamic Chart series

Posted on 2012-04-11
1
Medium Priority
?
274 Views
Last Modified: 2012-06-27
Hi Experts

I have a chart in Excel- one series formula is

=SERIES(BollingerBands!$A$1,,BollingerBands!$A$2:$A$500,3)

However the range A2:A500 populated values can change  - So sometimes I just need A2:A300 for instance

I have a formula to return the last cell address in the range A1:A500 that I want to use, but I dont know how to refernce that formula in the series - in this example in B1

something like =SERIES(BollingerBands!$A$1,B1,,3) - doesnt work, any ideas?
0
Comment
Question by:MrDavidThorn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 37834237
Here's a tip on creating charts from dynamic ranges:
http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html

While a formula could be embedded in the chart, I think the best way is to create a named range (e.g., myRangeValues) that represents your dynamic formula for a series dimension - e.g., to derive BollingerBands!$A$2:$A$500

Then, you should be able to use:

=SERIES(BollingerBands!$A$1,,BollingerBands!myRangeValues,3)

See attached.

Dave
dynamicChart-r2.xls
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question