Categorized graph with months

[mmahrous]

Hi,

Does anyone know how to achieve this kind of graph? (assuming the x's are numbers of course)

Thanks,

Mike

 

[Gerald Higgins]

There's two things going on here.

One is having the months sequential, with a separate entry for each "System".
The other is two lines on your X axis - one for System, one for month.

The first is easy, and is driven simply by your data layout.
Try moving the data for August, that is currently in D3:E8, into the range B9:C14, and then move September below that, into B15:C20.
If you don't want to do this to your actual data sheet, because you need to preserve the layout for some reason, then set up some other area in your worksheet that has the data laid out in this way, and make your chart depend on THAT area.

The second thing, with the two lines for the X axis, I've never done it myself, but try this...
https://peltiertech.com/chart-with-a-dual-category-axis/

 

[mmahrous]

Very good point thank you. But what if I need the chart to be dynamic when I make changes to this current worksheet with this preserved layout?

 

[mmahrous]

because I basically wanna avoid "double data entry" on separate worksheets

 

[Gerald Higgins]

You don't need double data entry.

If you want to preserve your data on Sheet 1, and have another area - let's say on Sheet 2 - to drive the chart, just make Sheet 2 link to Sheet 1 using formulas, so that you only need to enter data once on Sheet 1.

 

[mmahrous]

Sounds good! Thank you so much. What formulas do I need to use in this case?

 

[Gerald Higgins]

Just very simple formulas to repeat the value from another sheet.

For example, to repeat the value from Sheet 1, cell B3, use

=Sheet1!B3

 

[mmahrous]

ok thanks, I am gonna give it a try and let you know

 

[mmahrous]

There's two things going on here.

One is having the months sequential, with a separate entry for each "System".
The other is two lines on your X axis - one for System, one for month.

The first is easy, and is driven simply by your data layout.
Try moving the data for August, that is currently in D3:E8, into the range B9:C14, and then move September below that, into B15:C20.
If you don't want to do this to your actual data sheet, because you need to preserve the layout for some reason, then set up some other area in your worksheet that has the data laid out in this way, and make your chart depend on THAT area.

The second thing, with the two lines for the X axis, I've never done it myself, but try this...
https://peltiertech.com/chart-with-a-dual-category-axis/

If I move August and September below July in a separate worksheet like you said, how do I set up the table so that the charts work correctly ? Can I send you a sample workbook and you can show me an example?

 

[Gerald Higgins]

Please DON'T send me a sample workbook.

If you've set up your data in a different worksheet, in a different layout as suggested, presumably you now have something like this . . .

Data for JULY, IMPACTED EXPOSURES, 1.1 CID, in cell B3
Data for AUGUST, IMPACTED EXPOSURES, 1.1 CID, in cell B9
Data for SEPTEMBER, IMPACTED EXPOSURES, 1.1 CID, in cell B15
and so on, with all the other month/system figures in between . . . July 1.1 ESN Facility in B4, August 1.1 ESN Facility in B10, and so on.

Your chart data series for IMPACTED EXPOSURES is then simply all the data in column B.

And similarly for TOTAL EXPOSURES which will be all the data in column C.