Peltier Tech Blog

Excel Charting Utilities | Custom Solutions | Training | Charts and Tutorials | PTS Blog

 

Main menu:

 
Peltier Tech
Chart Utilities

PTS Waterfall Chart Utility
PTS Cluster-Stack Column Chart Utility
PTS Box and Whisker Chart Utility
PTS Marimekko Chart Utility
PTS Dot Plot Utility
PTS Cascade Chart Utility

Subscribe

Site search

Subscribe

Site search


Recent Posts

Recently Commented

Popular Posts

Archive


 

Categories


 

Buy me a coffee

If this topic or the whole blog has been helpful, support further development by treating me to a cup of coffee.


Privacy Policy

Creative Commons License
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.

Dynamic Ranges to Find and Plot Desired Columns

by Jon Peltier
Friday, April 11th, 2008
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.

Dynamic ranges (or “Names”) are commonly used to identify a range of the appropriate length, so that a chart will plot the right number of points. For example, you may want to plot year to date sales, without blanks for the months which are still in the future. The dynamic range uses COUNT or similar function to determine how many cells to include. As you add data, the range expands, and the chart automatically adjusts, so it plots all the months to date, but no months beyond that.

You can also use dynamic ranges if you don’t know what column your data is in. You can use MATCH and similar functions to determine which column has a particular header, and selectively plot the data in that column.

I was once given the following task:

We have two systems that produce similar data and need to chart both in a similar manner. However, the exact columns are not the same in each system’s output files and may even move from time to time. I need to do the following:

Find the columns containing “Elapsed Time”, “Azimuth”, “Elevation” and chart them with an XY chart with lines connecting the points. The column “Elapsed Time” must the the X axis, while “Azimuth” and “Elevation” are antenna pointing angles that must be plotted on the Y axis.

The following is a sample of the data, with irrelevant columns blanked for this exercise.

A B C D E F G H
1 Elevation Azimuth Elapsed Time
2 0 0 0:00:00
3 18 3.6 0:00:48
4 25.45584 7.2 0:01:36
5 31.17691 10.8 0:02:24
6 36 14.4 0:03:12
7 40.24922 18 0:04:00
8 44.09082 21.6 0:04:48
9 47.62352 25.2 0:05:36
10 50.91169 28.8 0:06:24
11 54 32.4 0:07:12
12 56.921 36 0:08:00
13 59.69925 39.6 0:08:48
14 62.35383 43.2 0:09:36
15 64.89992 46.8 0:10:24
16 67.34983 50.4 0:11:12
17 69.7137 54 0:12:00
18 72 57.6 0:12:48
19 74.2159 61.2 0:13:36
20 76.36753 64.8 0:14:24
21 78.46018 68.4 0:15:12
22 80.49845 72 0:16:00
23 82.48636 75.6 0:16:48
24 84.42748 79.2 0:17:36
25 86.32497 82.8 0:18:24
26 88.18163 86.4 0:19:12
27 90 90 0:20:00
28
29

This can be done with dynamic ranges. Assuming the labels are in row 1 of Sheet1, here are some defined names that construct the X and Y value ranges for your chart. Press CTRL+F3 to open the Defined Name dialog, and define these names:

Name: Sheet1!ET
Refers To: =MATCH(“Elapsed Time”,$1:$1,0)

Name: Sheet1!AZ
Refers To: =MATCH(“Azimuth”,$1:$1,0)

Name: Sheet1!EL
Refers To: =MATCH(“Elevation”,$1:$1,0)

(ET, AZ, and EL are the column numbers where these labels are found.)

Name: Sheet1!Xrows
Refers To: =MATCH(1E+307,INDEX($1:$65536,1,ET):INDEX($1:$65536,65536,ET),1)

(This is the number of rows used in the Elapsed Time column)

Name: Sheet1!TheX
Refers To: =(INDEX(Sheet1!$1:$65536,2,ET):INDEX(Sheet1!$1:$65536,Xrows,ET))

Name: Sheet1!TheAZ
Refers To: =(INDEX(Sheet1!$1:$65536,2,AZ):INDEX(Sheet1!$1:$65536,Xrows,AZ))

Name: Sheet1!TheEL
Refers To: =(INDEX(Sheet1!$1:$65536,2,EL):INDEX(Sheet1!$1:$65536,Xrows,EL))

(These are the data ranges under the labels.)

Let’s go to the VB Editor’s Immediate Window to see what we’ve got:

Now use the chart wizard to create a chart. In step 2, click on the Series tab, Select or Add the first series, name it “AZ”, and enter =Sheet1!theX in the X Values box and =Sheet1!theAZ in the Y Values box. Add the second series, again enter =Sheet1!theX in the X Values box, and enter =Sheet1!theEL in the Y Values box. No matter which column has the particular labels (and the left to right alignment doesn’t matter), the dynamic ranges will find the appropriate data for the chart.

Related Posts:

Bookmark and share this entry:
  • Twitter
  • Digg
  • Facebook
  • LinkedIn
  • del.icio.us
  • Technorati
  • StumbleUpon
  • Google Bookmarks
  • Reddit
  • MySpace
  • Slashdot
  • Yahoo! Buzz
  • Tumblr

Learn how to create Excel dashboards.

Write a comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.

If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Read the PTS Blog Comment Policy.





Subscribe without commenting

PTS Waterfall Chart Utility PTS Cluster-Stack Column Chart Utility PTS Box and Whisker Chart Utility PTS Marimekko Chart Utility PTS Dot Plot Utility PTS Cascade Chart Utility

Create Excel dashboards quickly with Plug-N-Play reports.