Single Line in Line Graph with Different Colors

Hello Everyone

I want to create a line graph with a line that changes colors across different values. For example, The Y-Axis will have values from 0-100. The line has a time series of number ranging from 0-100. What I want to do is that if the value as of specific dates are below 10, the Line color should be Green, if its below 10-33 Darker green, if between 33-66 then Yellow, if 66-90 then dark red, if above 90 then red.

The way I thought of doing it would be to create 5 different time series, each one with a different color and have them all on the same graph, that didnt work unfortunately. It should work but might need customization, could someone tell me how or possibly create an excel sheet with a column of =Random() and see how it can be done?

Thanks

Note: I do not want the whole graph to change color once a certain number is available but rather than history of the line to display the different colors simulatenously over the history.

Originally Posted by MrShorty

Adding the data as 5 different series is the most common way I know for "conditional formatting" of charts. Exactly how did you try to implement it? What didn't work? Was it something like this tutorial: https://peltiertech.com/conditional-...-excel-charts/

Thank you, I will try that out and let you know. I did slightly different thing which might make all the difference!

Originally Posted by MrShorty

Adding the data as 5 different series is the most common way I know for "conditional formatting" of charts. Exactly how did you try to implement it? What didn't work? Was it something like this tutorial: https://peltiertech.com/conditional-...-excel-charts/

Hello

Tried it and unfortunately it works and it doesn't. It doesn't work because unlike the data shown in that link my data goes in cycles over time which causes multiple lines to be formed.

I have uploaded a sample excel file. Please check it out and see how it can be fixed.

Originally Posted by MrShorty

This may not be popular on an Excel forum, but I opened your file in LibreOffice Calc, and it looked correct. As you note, even with the "leave blanks as gaps" setting, Excel draws a line across the interior N/A's. LO Calc's chart engine left gaps over the N/A's. In this respect, it seems that LO Calc has a superior "leave blanks as gaps" charting algorithm. Of course, switching spreadsheet applications will create other problems (I often find that Excel and Calc's charting engines are not easily compatible with each other), so switching applications may not be a good choice. However, it appears to be a quick and easy solution for the specific problem in the specific sample file attached.

How automated does your solution need to be? As noted, this is a "bug/feature" of how Excel interprets the hidden and empty cells setting related to non-empty N/A cells. If any of those offending N/A cells was truly blank, then Excel would leave a proper gap in the chart. In your file, I selected E12 and pressed delete, then F17 and pressed delete, and the extra lines disappeared from the chart. It was quick and easy for the specific example given, even if it did require manual input. It will be easy to refill those two cells in for future data sets, and then delete a few cells as needed. This doesn't bode well for larger data sets or for data sets that are more complex -- especially if they result in many extra lines on the chart. This is simple and easy, but will become tedious for larger, more complex data sets -- like when expanding the data range to include all 2000 rows. Judge for yourself whether this is easier than trying to come up with a fully automatic solution.

I expect that a fully automatic solution is going to involve expanding the existing solution so that the formulas will recognize each "gap" in the individual data sets, and add each unique segment as a separate column/data series. I have not explored exactly what this formula would need to look like, nor have I searched your full data set to see how many different columns/data series would be needed. In the end, it amounts to the same basic approach, except that the "logic" behind the formula necessarily becomes more complex. If you need to explore this, let us know and we can look at it.

The previous approach (each color added as a separate data series) is most common because it is considered easier than having VBA format each segment/point of the original, full data series. With this kind of complexity, it may become easier to write a VBA macro that will do the conditional formatting rather than the spreadsheet approach. I don't know when this becomes easier, but it is something to keep in mind.

I don't know what LibreOffice Calc is and never used it but might explore it to see how viable it could or could not be.
In terms of complexity, I want to do the graph using different ranges of 12-5000 rows and so once it works on one it will work on the others. The sample I gave you is a random but 90% similar type of numbers so if you could fix that then it would solve everything.
As you said when you use all the rows there are lots of lines so that won't work. The data also changes as everyday a new value is added and so the N/A and so on shift down one every day and new N/A will be present so deleting formulas isn't a solution.

Any VBA solution would be as perfect as possible, just so you understand the Y values tab in my real excel file is calculated based on 12 other cells which pulls data from online hence the daily change.

Thanks