Line chart with conditional formatting

[sharshra]

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.

Date	Sales	up	down
01-Nov-21	1,011	#N/A	1011
02-Nov-21	746	#N/A	746
03-Nov-21	638	#N/A	638
04-Nov-21	594	#N/A	594
05-Nov-21	576	#N/A	576
06-Nov-21	543	#N/A	543
07-Nov-21	601	601	#N/A
08-Nov-21	657	657	#N/A
09-Nov-21	675	675	#N/A
10-Nov-21	494	#N/A	494
11-Nov-21	492	#N/A	492
12-Nov-21	358	#N/A	358
13-Nov-21	55	#N/A	55
14-Nov-21	65	65	#N/A
15-Nov-21	550	550	#N/A

 

[6StringJazzer]

If the formatting is based on each set of two adjacent points, you need three sets of data. It doesn't matter what your data looks like. You will never have to change the way the table is set up, just the number of rows.