Two Colors in Same Series Graph

[prasaddn]

Hi,

I was checking how to plot different color of same series chart like % of actuals above SLA% and below SLA%.

I did find a very good solution from Andy Pope's site, that it can be done either with formula or with VBA.

I am able to get the result replicated in my book, except for one challenge. How to change the x axis to reflect months instead of normal number/units.

Attached is the actual file from Andy Pope's site and my file both.

 

[prasaddn]

Sorry, posted before I could complete...

Original file name: copy of conditionalLine2
and my file name: graph... line

Regards,
Prasad DN

 

[Luke M]

Your chart was getting it's x-axis values from col D and N. In the example, you had just been using numbers 1-6. But, since Dates are also a form of number in XL, we can change this to just be our dates, and the formulas work. With the dates in place, we tweak the formatting, and our chart looks very nice.

Note that when setting our new min/max, XL is smart enough (now) to let us type in a date format like 10/1/2014, and it can accept it.

 

[prasaddn]

Hi Luke,

Thank you looking into this issue.

As you suggested, I did try dates as data points, and the output was not exactly what I wanted.

In your file as well, I see x axis having two oct-14, Nov-14 and Jan-15 data points, while I need to show the graph with only 6 points, i mean the months in data.

I tried to adjust the x axis major changes to 30 days, but still the result was showing two oct 14 data points.

kindly advise.

Regards,
Prasad DN

 

[Luke M]

October has 31 days, so the first axis point was Oct 1, 2014 and the last was Oct 31, 2014. You can either change the formatting of axis to show days to alleviate confusion, or for a short time duration like this one, set the major change to be 31, and make the max value on axis be Mar 5, 2015.

 

[prasaddn]

October has 31 days, .....

good catch!! And as coincidence the 2nd point was exactly on 31st.

Thank you Luke!
Prasad