Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
James Hobart
 
Posts: n/a
Default Want to combine columns and horizontal lines in complex chart - Can it be Done?!


Hello All,

I want to create a complex chart that has 13 pairs of vertical columns
representing before and after school attendance for 13 schools. I want
to superimpose 13 sets of 5 horizontal lines that represent attendance
thresholds for each school.

The source for the position of the lines comes from columns SCR$,
UpperU, UpperA, Optimal, LowerA and LowerU. The source of data for the
columns comes from the columns Base and New.

Here is a chart of how my data is imported - I can of course,
manipulate that import or transpose it if necessary.

SchoolNum SCR$ UpperU UpperA Optimal LowerA LowerU Base New
8 583 496 443 420 396 350 382 369
13 436 371 331 314 296 262 418 380
23 192 163 146 138 131 115 203 147
25 202 172 154 145 137 121 155 173
27 728 619 553 524 495 437 299 528
47 644 547 489 464 438 386 420 419
83 796 677 605 573 541 478 797 655
85 555 472 422 400 377 333 535 696
86 216 184 164 156 147 130 0 0
101 490 417 372 353 333 294 276 277
102 617 524 469 444 420 370 387 387
215 664 564 505 478 452 398 601 446
237 247 210 188 178 168 148 172 168

I will be importing this data from another program that generates one
image per school. I have attached a bmp that shows how I want each
school to be represented with columns and horizontal lines. The
horizontal lines should not be connected between the schools!

Thanks,

James Hobart


+-------------------------------------------------------------------+
|Filename: New Picture.bmp |
|Download: http://www.excelforum.com/attachment.php?postid=2998 |
+-------------------------------------------------------------------+

--
James Hobart
------------------------------------------------------------------------
James Hobart's Profile: http://www.excelforum.com/member.php...o&userid=17540
View this thread: http://www.excelforum.com/showthread...hreadid=346024

  #2   Report Post  
Barb Reinhardt
 
Posts: n/a
Default

I think I'd create a line chart using all of the data with the data in SCR$
as the X axis label. I'd then right click on the BASE series and change
the chart type to COLUMN. I'd do the same with the NEW series. That
should get you part of the way.

"James Hobart" wrote in message
...

Hello All,

I want to create a complex chart that has 13 pairs of vertical columns
representing before and after school attendance for 13 schools. I want
to superimpose 13 sets of 5 horizontal lines that represent attendance
thresholds for each school.

The source for the position of the lines comes from columns SCR$,
UpperU, UpperA, Optimal, LowerA and LowerU. The source of data for the
columns comes from the columns Base and New.

Here is a chart of how my data is imported - I can of course,
manipulate that import or transpose it if necessary.

SchoolNum SCR$ UpperU UpperA Optimal LowerA LowerU Base New
8 583 496 443 420 396 350 382 369
13 436 371 331 314 296 262 418 380
23 192 163 146 138 131 115 203 147
25 202 172 154 145 137 121 155 173
27 728 619 553 524 495 437 299 528
47 644 547 489 464 438 386 420 419
83 796 677 605 573 541 478 797 655
85 555 472 422 400 377 333 535 696
86 216 184 164 156 147 130 0 0
101 490 417 372 353 333 294 276 277
102 617 524 469 444 420 370 387 387
215 664 564 505 478 452 398 601 446
237 247 210 188 178 168 148 172 168

I will be importing this data from another program that generates one
image per school. I have attached a bmp that shows how I want each
school to be represented with columns and horizontal lines. The
horizontal lines should not be connected between the schools!

Thanks,

James Hobart


+-------------------------------------------------------------------+
|Filename: New Picture.bmp |
|Download: http://www.excelforum.com/attachment.php?postid=2998 |
+-------------------------------------------------------------------+

--
James Hobart
------------------------------------------------------------------------
James Hobart's Profile:

http://www.excelforum.com/member.php...o&userid=17540
View this thread: http://www.excelforum.com/showthread...hreadid=346024



  #3   Report Post  
James Hobart
 
Posts: n/a
Default


I'm a newbie at this so please exuse me. I have looked for an example
of this on the many posts and websites but haven't found my
solution...

Please look at the bmp attached to the original message - this would be
a snapshot of just one pair of stacked columns. For each pair of
stacked columns there are a different set (with different values) of 5
horizontal lines. The main problem is I don't want these five lines
interconnected at all between the stack columns. They should be
completely horizontal and only cover the area where the stacked columns
occur. In other words, the values of the lines relate only to the
values of the stacked columns.

Conversely, is there a way to combine 13 charts into one? If there
were a workaround to create one cluster of two columns with the 5 flat
line values then I could combine the 13 together?


--
James Hobart
------------------------------------------------------------------------
James Hobart's Profile: http://www.excelforum.com/member.php...o&userid=17540
View this thread: http://www.excelforum.com/showthread...hreadid=346024

  #4   Report Post  
Jon Peltier
 
Posts: n/a
Default

That you Pete?

Here's how I explained this chart in another forum:

I inserted a column of data, column A in the attached, which has the
numbers 0.5, 1.5, 2.5 through 12.5. We'll use this later.

In the first chart, I've made a clustered column chart using column B
for category labels, column I for one series' values, and column J for
the other series values. I set the overlap to -50 and the gap width to 100.

I copied the range A2:A15,D2:H15 (hold down CTRL to select a multiple
area range). I selected the chart, and used Paste Special (Edit menu) to
add the data as New Series, in Columns, First Row as Series Names, First
Column as Category Labels. This is the cluttered column chart in chart 2.

Chart 3 shows all of the added series converted to XY Scatter series. I
selected one, used Chart Type (Chart menu) to change it to a Scatter
type without markers. Then I selected each of the others and used the F4
key to repeat this action.

I used Chart Options (Chart menu) to remove the secondary axes, by
unchecking them on the Axes tab. See chart 4.

I formatted each scatter series (double click) to use no marker
(Patterns tab) and to have a positive X error bar of value 1. I
formatted each error bar series to have the desired color, and to use
the error bar style without the cross at the end. See chart 5.

The sample workbook is he

http://PeltierTech.com/Excel/Zips/BaltimoreSchools.zip

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


James Hobart wrote:

Hello All,

I want to create a complex chart that has 13 pairs of vertical columns
representing before and after school attendance for 13 schools. I want
to superimpose 13 sets of 5 horizontal lines that represent attendance
thresholds for each school.

The source for the position of the lines comes from columns SCR$,
UpperU, UpperA, Optimal, LowerA and LowerU. The source of data for the
columns comes from the columns Base and New.

Here is a chart of how my data is imported - I can of course,
manipulate that import or transpose it if necessary.

SchoolNum SCR$ UpperU UpperA Optimal LowerA LowerU Base New
8 583 496 443 420 396 350 382 369
13 436 371 331 314 296 262 418 380
23 192 163 146 138 131 115 203 147
25 202 172 154 145 137 121 155 173
27 728 619 553 524 495 437 299 528
47 644 547 489 464 438 386 420 419
83 796 677 605 573 541 478 797 655
85 555 472 422 400 377 333 535 696
86 216 184 164 156 147 130 0 0
101 490 417 372 353 333 294 276 277
102 617 524 469 444 420 370 387 387
215 664 564 505 478 452 398 601 446
237 247 210 188 178 168 148 172 168

I will be importing this data from another program that generates one
image per school. I have attached a bmp that shows how I want each
school to be represented with columns and horizontal lines. The
horizontal lines should not be connected between the schools!

Thanks,

James Hobart


+-------------------------------------------------------------------+
|Filename: New Picture.bmp |
|Download: http://www.excelforum.com/attachment.php?postid=2998 |
+-------------------------------------------------------------------+

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel moving horizontal columns to vertical doulos Excel Discussion (Misc queries) 1 February 4th 05 07:01 PM
Vertical ND Curve on Combination Chart Phil Hageman Charts and Charting in Excel 4 December 30th 04 07:07 PM
Making Rows into Columns Tony Williams Excel Discussion (Misc queries) 1 December 2nd 04 04:47 PM


All times are GMT +1. The time now is 09:52 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"