Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel moving horizontal columns to vertical | Excel Discussion (Misc queries) | |||
Vertical ND Curve on Combination Chart | Charts and Charting in Excel | |||
Making Rows into Columns | Excel Discussion (Misc queries) |