Line chart with conditional formatting

sharshra

Active Member
Joined
Mar 20, 2013
Messages
270
Office Version
  1. 365
I have made a chart with conditional formatting to indicate up & down trends. It works correctly when the trend is up or down. But when the trend changes, it doesn't give the correct result. Not sure how to correct this. Can someone suggest to resolve the issue?

Table & chart is given below for illustration. Date & sales are actual columns. Columns up & down are added for conditional formatting. As seen in the chart, when the trend is up, red line is there & when trend is down, green line is used. But when trend changes from down to up or up to down, it doesn't correspond to red or green colour lines. These transitions are circled in the chart below for quick reference.
DateSalesupdown
01-Nov-211,011#N/A1011
02-Nov-21746#N/A746
03-Nov-21638#N/A638
04-Nov-21594#N/A594
05-Nov-21576#N/A576
06-Nov-21543#N/A543
07-Nov-21601601#N/A
08-Nov-21657657#N/A
09-Nov-21675675#N/A
10-Nov-21494#N/A494
11-Nov-21492#N/A492
12-Nov-21358#N/A358
13-Nov-2155#N/A55
14-Nov-216565#N/A
15-Nov-21550550#N/A


1639447850221.png
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
I've done this type of chart before. You have to enhance your formulas so that when the line reverses direction you need to have the first value in both columns. For example, you must include 543 in the Up column; you must include 675 in the Down column.

If you include your actual formulas instead of just the displayed values I could help with the formulas, if you need it.
 
Upvote 0
Thanks, Jeff. I have the following formula to fill the up & down columns. It basically compares the previous value & fill either up or down columns accordingly.

Up column -
Excel Formula:
=IF(C6>C5,C6,NA())
Down column -
Excel Formula:
=IF(C6<=C5,C6,NA())
 
Upvote 0
Up column:
Excel Formula:
=IF(OR(C7>C6,C6>C5),C6,NA())

Down column:
Excel Formula:
=IF(OR(C6<=C5,C7<=C6),C6,NA())
 
Upvote 0
Thanks, Jeff. It works correctly when the next number & the number after that follow the same trend. But, if there is an alternate up & down, chart doesn't show the correct colored lines.

For example, consider the table below. Red line indicate up & green for down. Circled areas indicate incorrect line colors.
DateSalesupdown
01-Nov-21950950950
02-Nov-21960960960
03-Nov-21638638638
04-Nov-21750750750
05-Nov-21576576576
06-Nov-21580580#N/A
07-Nov-21590590#N/A
08-Nov-21657657657
09-Nov-21499499499
10-Nov-21675675675
11-Nov-21492#N/A492
12-Nov-21358#N/A358
13-Nov-21555555
14-Nov-216565#N/A
15-Nov-21550550550

1639522923208.png
 
Upvote 0
Can you capture your sheet again and this time click the option that says "formulas"? It is very difficult doing this without having a file that has the chart. If you have a way to share the file it would ideal.
 
Upvote 0
Hi Jeff,
I don't see any option to share the file. If there are any options, please suggest. Now, I have copied the mini sheet with formula.
line chart trial.xlsx
BCDE
5DateSalesupdown
601-Nov-21950950950
702-Nov-21960960960
803-Nov-21638638638
904-Nov-21750750750
1005-Nov-21576576576
1106-Nov-21580580#N/A
1207-Nov-21590590#N/A
1308-Nov-21657657657
1409-Nov-21499499499
1510-Nov-21675675675
1611-Nov-21492#N/A492
1712-Nov-21358#N/A358
1813-Nov-21555555
1914-Nov-216565#N/A
2015-Nov-21550550550
summary
Cell Formulas
RangeFormula
D6:D20D6=IF(OR(C7>C6,C6>C5),C6,NA())
E6:E20E6=IF(OR(C6<=C5,C7<=C6),C6,NA())

1639522923208-png.53381
 
Upvote 0
That helps. This forum does not offer anyway to share a file but a lot of people use services like Dropbox or OneDrive to share files. I'll have a look.
 
Upvote 0
I went back to look at what I did earlier and I realized that I solved a different problem. My lines turned red when the data went below 0. That is different than changing color when the line changes direction, and is a much easier problem.

Here is the problem with what you want to do. Let's imagine the case where you have a complete zig-zag. The line changes direction on every point. That means every point is a member of a pair of values that rise, and every point is a member of a pair of values that fall. Excel does not plot lines, it plots points, and then connects the points with lines. So for your "up" lines every point would be plotted, and so every point would be connected with the "up" color. Same for down.

Frankly I didn't think it was possible but I had a flash of inspiration and I did figure out a way to do want you want. You must have three "up" series and three "down" series based on the original data. Each series staggers the data so that you have disconnected value pairs in each series. Since each pair plus one empty cell is three, you must have a cycle of three series.

Here is a link to the sample file.

Below is the data and chart.

$scratch.xlsm
BCDEFGHI
5DateSalesup 1down 1up 2down 2up 3down 3
61-Nov-21950950#N/A
72-Nov-21960960#N/A#N/A960
83-Nov-21638#N/A638638#N/A
94-Nov-21750#N/A750750#N/A
105-Nov-21576#N/A576576#N/A
116-Nov-21580580#N/A580#N/A
127-Nov-21590590#N/A590#N/A
138-Nov-21657657#N/A#N/A657
149-Nov-21499#N/A499499#N/A
1510-Nov-21675#N/A675675#N/A
1611-Nov-21492#N/A492#N/A492
1712-Nov-21358#N/A358#N/A358
1813-Nov-215555#N/A#N/A55
1914-Nov-216565#N/A65#N/A
2015-Nov-21550550#N/A#N/A550
up down chart
Cell Formulas
RangeFormula
D6,H20,F19,D18,H17,F16,D15,H14,F13,D12,H11,F10,D9,H8,F7D6=IF($C6<$C7,$C6,NA())
E6,I20,G19,E18,I17,G16,E15,I14,G13,E12,I11,G10,E9,I8,G7E6=IF($C6>$C7,$C6,NA())
D7,F20,D19,H18,F17,D16,H15,F14,D13,H12,F11,D10,H9,F8D7=IF($C7>$C6,$C7,NA())
E7,G20,E19,I18,G17,E16,I15,G14,E13,I12,G11,E10,I9,G8E7=IF($C7<$C6,$C7,NA())


Here is what the resulting chart looks like:
up down chart.jpg
 
Upvote 0
Thank you very much, Jeff. You are of great help ?

It fixed the problem for now. I'm just wondering will it require further manipulations if the pattern changes? For example, can there be a scenario where I may have to use up 4, down 4, up 5, down 5 etc? The table may contain hundreds of rows of data & the pattern may vary.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top