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

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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?

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 here:

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 |
> +-------------------------------------------------------------------+
>