google-sheetsplotgoogle-sheets-formulagoogle-visualization

How can I color the increasing and decreasing slopes on a line chart?


I'm creating a line chart in Google Spreadsheets. How can I display the increasing and decreasing sections in different colors?

I am attempting to do this by creating auxiliary columns with formulas and overlaying them onto the original chart (here, column B contains the data to be displayed and the formulas are set as if they were in cells C2 and D2):

=if(($B2>$B3)+($B2<$B1), $B2, na())
=if(($B2<=$B3)*($B3>$B4)+($B1<=$B2)*($B2>$B3), $B2, na())

This is the current result with incorrectly colored sections 12-13, 23-24, and 28-29 (the issue arises with zigzag sections like [1, 2, 1, 2, 1, 2], where increasing and decreasing alternate at every step):

Chart with incorrectly colored decreasing sections

I wonder if it's possible to write a formula that separates different trends into individual columns or inserts an additional empty half-step between adjacent trends.

Experimentation Document

The data I'm working with:

Step Value
1 9577
2 9601
3 9454
4 9329
5 9422
6 9238
7 9517
8 9589
9 9603
10 9564
11 9610
12 9623
13 9616
14 9633
15 9249
16 9525
17 9549
18 9498
19 9391
20 9512
21 9530
22 9550
23 9565
24 9541
25 9568
26 9567
27 9571
28 9595
29 9527
30 9621
31 9566

Solution

  • Step Value Split1 Split2 Difference Increase Decrease Step
    1 9577 9577 9601 -24 1
    2 9601 9601 9454 147 9577 1
    3 9454 9454 9329 125 9601 2
    4 9329 9329 9422 -93 9601 2
    5 9422 9422 9238 184 9454 3
    6 9238 9238 9517 -279 9454 3
    7 9517 9517 9589 -72 9329 4
    8 9589 9589 9603 -14 9329 4
    9 9603 9603 9564 39 9422 5
    10 9564 9564 9610 -46 9422 5
    11 9610 9610 9623 -13 9238 6
    12 9623 9623 9616 7 9238 6
    13 9616 9616 9633 -17 9517 7
    14 9633 9633 9249 384 9517 7
    15 9249 9249 9525 -276 9589 8
    16 9525 9525 9549 -24 9589 8
    17 9549 9549 9498 51 9603 9
    18 9498 9498 9391 107 9603 9
    19 9391 9391 9512 -121 9564 10
    20 9512 9512 9530 -18 9564 10
    21 9530 9530 9550 -20 9610 11
    22 9550 9550 9565 -15 9610 11
    23 9565 9565 9541 24 9623 12
    24 9541 9541 9568 -27 9623 12
    25 9568 9568 9567 1 9616 13
    26 9567 9567 9571 -4 9616 13
    27 9571 9571 9595 -24 9633 14
    28 9595 9595 9527 68 9633 14
    29 9527 9527 9621 -94 9249 15
    30 9621 9621 9566 55 9249 15
    31 9566 9525 16
    9525 16
    9549 17
    9549 17
    9498 18
    9498 18
    9391 19
    9391 19
    9512 20
    9512 20
    9530 21
    9530 21
    9550 22
    9550 22
    9565 23
    9565 23
    9541 24
    9541 24
    9568 25
    9568 25
    9567 26
    9567 26
    9571 27
    9571 27
    9595 28
    9595 28
    9527 29
    9527 29
    9621 30
    9621 30
    9566 31
    31

    Using X axis as Column H and Series as Column F and G, we can get the desired result.