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):
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.
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 |
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 |
The first 2 columns are your data and the last 3 columns are the final result needed to form the chart. The rest are intermediate steps(which can be collapsed).
We split the data into two sets of data points per row. Each data point will repeat twice. This is done to calculate the difference. In C2,
=WRAPROWS( REDUCE(B2,B3:B32,LAMBDA(a,c,IF(COUNT(a)=1,{a;c},{a;INDEX(a,COUNTA(a));c}))),2)
We calculate the consecutive difference in E2:
=ARRAYFORMULA(C2:C31-D2:D31)
Then we split the data points again to calculate sets of increases or decreases in F1:
=REDUCE(
{"Increase", "Decrease";"",""},
E2:E31,
LAMBDA(a,c,
LET(
r,TOCOL(OFFSET(c,0,-2,1,2)),
e,VSTACK("",""),
VSTACK(a,IF(c<0,HSTACK(r,e),HSTACK(e,r))))))
We add the Step back as doubled sequential numbers in H1:
=REDUCE("Step",A2:A36,LAMBDA(a,c,VSTACK(a,c,c)))
Using X axis as Column H and Series as Column F and G, we can get the desired result.