broken column: how to apply with 2 data sets on X-axis?

[elguapoyo]

Greetings from Virginia,

I'm currently working on my doctorate thesis due next week. I managed to make a broken column but I’m in a real bind now! I’m working with graph that has two different data sets of time across the X-axis. I can make a broken column but I can only do so separate from the other data and I need to have both on the same x-axis. I’ve tried a number of things. I’d be very grateful if you might have any ideas on how to resolve this.

[dflak]

Which two X-Axis do you have to combine and on what chart?

[elguapoyo]

Hi dflak,

Thank you so much for your response!

I am working on the first three (OP, 10P, and 50P) column charts in the excel attachment. The outlier - citric s - for the first 24hrs. is what I am trying to
break since it is much larger than the rest. I was able to create a chart with the broken citric s column using the 24 hr. data set. However, I don't know how
to combine the 24hr. columns (including the citric s broken column) in one chart with the 120hr. columns, so that they are both side by side. Essentially, I need
to make a chart exactly like the first three that I already have, only thing different would be the broken column of citric s in the first 24hr. data set.
Hopefully my explanations make sense. Please let me know if anything else is unclear. And again, can't thank you enough for looking into it!

Best regards,

Jerry

[dflak]

I'm still a bit fuzzy on what you want to do, but it sounds like this: http://peltiertech.com/broken-y-axis-in-excel-chart/.

[elguapoyo]

Yes! The second example of the broken axis is exactly how I want my columns.
But like it says, it’s cumbersome to create and nearly impossible to maintain.
The panel chart might be an alternative, but I haven't been able to do that either.

[dflak]

Here's one way of doing it: use two charts.

Columns A:B have the original data.

Cell F1 has the formula: =MAX(B2:B6). This gets the maximum value.

Cell F2 has the formula: =MAX(IF(B2:B6=F1,FALSE,B2:B6)) entered as an array formula. This gets the next highest maximum value.

Column C has the formula: =IF(B2<>$F$1,B2,$F$2) - this formula "caps" all the bars at the next lowest maximum value.

Column D has the formula: =IF(B2=$F$1,$F$1,NA()) - this formula plots only the maximum value.

[dflak]

I just did some more playing and I like this version better. The top chart shows the actual comparison. The bottom chart shows the detail of the remainders.

[elguapoyo]

Hi dflak!

I've tried working with what you've done but I'm still running into the same problem I had with the broken column.. Because the data I am working with is composed of two sets: 24hrs. and 120hrs., I need them both on the same X-axis. But I can only get this method, as well as the method with the broken column, to work if there is only one set of data. My attempts to combine them only add to the existing columns, not separate. If you still have the time, check out my excel sheet. I hope what I'm saying is not too fuzzy.
I can't thank you enough for your help!

Regards,

Jarrett

[dflak]

Can you mock up a sample of what you want the output to look like based on the data you have?

[dflak]

Here is a sample using some of your data. The full chart is the data exactly as you have it. The Detail Chart is based on data with formulas. Cells I2 and I3 have the formulas for MAX and next MAX.

[elguapoyo]

Wow. Thanks so much! This seems a little more straightforward than the broken columns, but I'm not very adept with excel in the first place.I'm going to try and follow the website you linked in order to get the others. I'd like to send you some money for you time but I don't see anywhere with an 'appreciation' button.

[dflak]

No need to send money. I'm a believer in "paying it forward." I just happened to be in the right place at the right time with the right stuff to help. So when you wind up in the right place at the right time with the right stuff to help someone else, I'll consider myself repaid. Eventually it comes back in one form or another.

[elguapoyo]

Thanks again! You're doing Gods' work. I make sure to keep paying it forward.
All the best to you,

Jerry