Visual trend indicators

[paulkc]

I want to format cells base on a continued trend up or down. For example, if cells a2:g2 contain values that decrease each successive cell gets a stronger red. On the other hand, if they are increasing, they would get formatted in stronger greens. Is this even remotely possible?

 

[BSALV]

Map1
	A	B
2	23,32920666	1
3	8,799134951	2
4	1,057575445	3
5	8,591968641	4
6	5,060329058	5
7	-1,009101948	-1
8	20,89381874	1
9	29,76579601	2
10	-1,219482284	-1
11	7,403154143	1
12	3,129408267	2
13	22,74725258	3
14	8,967713029	4
15	4,377110611	5
16	13,91193299	6
17	-3,341311556	-1
18	-1,71809094	-2
19	14,38264771	1
20	15,9209699	2
21	8,818923042	3
22	10,87578939	4
23	24,64068868	5
24	10,65308681	6
25	16,50309979	7
26	27,26213821	8
27	27,10156544	9
28	-0,641707551	-1
29	9,414956528	1
30	8,418036149	2
31	-2,873287388	-1
32	28,97850144	1
33	29,95790026	2
34	29,43275356	3
35	9,875787813	4
36	2,120550542	5
37	28,62829808	6
38	8,300359911	7
39	24,81020589	8
40	29,53301064	9
41	9,805265601	10
42	7,248307527	11
43	19,29068452	12
44	17,92240199	13
45	-3,670679385	-1
46	2,518817912	1
47	25,16702232	2
48	-0,96252753	-1
49	12,16956637	1
50	0,325867414	2
51	26,68988343	3
52	4,77569873	4
53	19,90802542	5
54	12,35055303	6
55	12,33611336	7
56	3,698108879	8
57	-1,755852633	-1
58	15,87674364	1
59	24,24157219	2
60	22,45080382	3
61	0,90222926	4
62	13,04579036	5
63	-2,206148656	-1
64	-3,845610364	-2
65	0,740054457	1
66	18,22057372	2
67	28,84287484	3
68	21,85068979	4
69	-2,542726765	-1
70	18,73282874	1
71	6,840855055	2
72	15,79847328	3
73	7,983372711	4
74	26,29759002	5
75	-0,816185281	-1
76	3,530761937	1
77	12,32066546	2
78	29,86458604	3
79	18,88028739	4
80	27,13802945	5
81	8,528005356	6
82	19,86491954	7
83	4,630597887	8
84	1,212026063	9
85	17,72195059	10
86	14,68973862	11
87	4,058669847	12
88	23,67627474	13
89	-1,433072659	-1
90	0,620724642	1
91	7,380868142	2
92	16,68996216	3
93	-3,967751701	-1
94	21,11186194	1
95	14,51230665	2
96	26,08518307	3
97	9,951424201	4
98	17,25644818	5
99	22,68434397	6
100	23,19049027	7
101	0,826611098	8
Blad2

Cell Formulas
Range	Formula
A2:A101	A2	=RANDARRAY(100,,-4,30)
B2:B101	B2	=SUM(IF(COUNT(B1,A2)<>2,0,IF(SIGN(B1)=SIGN(A2),B1)),SIGN(A2))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B1001	Other Type	DataBar		NO

 

[paulkc]

Here is a sample of the data I'm working with. I think what you are showing me might work but I'm not sure how to apply it to this data. This is actually a pivot table and will continue to grow as I add data to the underlying table. This is a percentage of completed assignments. As the year progresses the "target" percentage continues to increase. What I currently have is conditional formatting showing me that it is not meeting the target. But, what I really want to see is whether they are trending farther and farther away from that target over time. Or, on the other hand, are they holding their own (they might not be reaching the target but they are not getting any farther behind). It would be nice to see a positive trend as well.

Student Progress Eval.xlsm
	E	F	G	H	I	J	K	L	M	N
4	4/26/2022	4/27/2022	4/28/2022	4/29/2022	5/2/2022	5/3/2022	5/4/2022	5/5/2022	5/6/2022	5/9/2022
5
6
7	76.80	77.96	79.20	80.58	82.00	82.74	83.96	85.74	86.70	88.02
8	77.40	79.00	80.60	82.30	82.30	83.90	85.50	85.50	85.50	87.10
9	79.50	80.80	82.20	83.60	83.60	83.60	84.90	86.30	89.00	89.00
10	77.40	77.40	79.60	80.60	81.70	82.80	83.90	84.90	86.00	88.20
11	76.50	77.60	78.60	79.60	80.60	81.60	83.70	84.70	85.70	86.70
12	73.20	75.00	75.00	76.80	81.80	81.80	81.80	87.30	87.30	89.10
13	54.40	55.27	55.27	56.00	58.40	61.00	62.03	62.90	62.90	62.90
14	62.70	64.20	64.20	64.20	67.20	67.20	67.20	68.70	68.70	68.70
15	45.10	46.20	46.20	48.40	49.50	52.70	52.70	53.80	53.80	53.80
16	55.40	55.40	55.40	55.40	58.50	63.10	66.20	66.20	66.20	66.20
17	69.66	70.26	72.46	74.30	76.00	76.48	77.02	78.38	79.24	80.96
18	77.60	77.90	79.20	81.80	84.40	85.70	85.70	88.30	89.60	89.60
19	77.80	79.40	85.70	85.70	85.70	85.70	85.70	87.30	87.30	88.90
20	73.00	73.00	75.30	75.30	78.70	78.70	78.70	79.80	80.90	85.40
21	47.80	48.90	50.00	52.20	53.30	54.40	55.60	55.60	56.00	57.10
22	72.10	72.10	72.10	76.50	77.90	77.90	79.40	80.90	82.40	83.80
23	65.02	67.16	68.62	69.94	70.68	71.38	72.90	76.12	77.74	79.34
NewSummary

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:O130	Cell Value	<NETWORKDAYS(Calendar!$B$5,B$4,Calendar!$B$13:$B$42)/Calendar!$B$6*100	text	NO

 

[BSALV]

a variant on this ?
conditional

Sub kleuren()
     Dim iSgn
     With Range("A5:w21")     'your range
          For i = 1 To .Rows.Count     'loop through the rows
               ptr = 0     'reset counter
               For j = 2 To .Columns.Count     'loop through the columns
                    iSgn = Sgn(.Cells(i, j) - .Cells(i, j - 1))     'actual direction (down=-1, equal=0,up=+1)
                    ptr = -ptr * ((iSgn = Sgn(ptr)) Or (iSgn = 0)) + iSgn     'counter direction
                    maincolor = Application.Min(255, 255 - Abs(ptr) * 5)     'maincolor for green or red in RGB
                    auxcolor = Application.Max(1, 200 - Abs(ptr) * 20)     'the 2 other colors in RGB
                    Select Case ptr
                         Case -1 To 1: .Cells(i, j).Interior.ColorIndex = xlNone
                         Case Is < -1: .Cells(i, j).Interior.Color = RGB(maincolor, auxcolor, auxcolor)
                         Case Else: .Cells(i, j).Interior.Color = RGB(auxcolor, maincolor, auxcolor)
                    End Select
               Next
          Next
     End With
End Sub

 

[paulkc]

That is definitely getting closer to what I'm aiming for. But, the issue is that it's being based on whether the number increased from one day to the next. As a general rule, they will never go down. But, if the number doesn't change it's a negative trend because the number should have increased. So, let's just say that on Monday, their goal is to be at 72% and they are at 71%. Then on Tuesday, they are only at 71.5% but they should be at 73%. This would be a negative trend even though there was an increase. Here is the formula I'm using in my current condition formatting: =NETWORKDAYS(Calendar!$B$5,B$4,Calendar!$B$13:$B$42)/Calendar!$B$6*100

How would I use that condition in the VBA?

 

[Jon Peltier]

View attachment 63903

FYI the solid data bars are better in terms of precognitive comprehension than gradient shaded bars.

 

[BSALV]

@paulkc, this is the row where you check the trend, but i didn't understand the formula, can you give an example

  iSgn = Sgn(.Cells(i, j) - .Cells(i, j - 1))     'actual direction (down=-1, equal=0,up=+1)

@jon Peltrier, your reaction is about #2, not #4, i assume and i don't see a method to use it with what @paulkc demands.

 

[paulkc]

The data examples shown are based on assignments that should have been completed up to the current day. For example, let's say we have a term of 90 days. After the 5th day, this student should be at 5.6% (5/90), after 10 days, he would be at 11.1% (10/90), etc.
Now, let's say that this student was at 4.9% after the first week and at 9.8% after the 2nd. This would be a negative trend because even though he did the same amount of work both weeks (4.9%), his schedule requires 5.6%. So, after the 2nd week, he is farther behind than he was the first week.
The formula I posted in #5 is calculating that expected percentage by calculating the number of workdays between the start of the term which is defined on the Calendar tab in cell B5 and the current day shown above in row 4.

 

[BSALV]

Can you add a link with (that part of) your spreadsheet. With XL2BB it starts in column E and the formulas use B-cells.

 

[paulkc]

Student Progress Eval.xlsm
	A	B	C	D
1	First Day	2021-08-24		89
2	End 1st	2022-01-14
3	End 2nd	2022-05-27
4
5	Current Term Start	2022-01-17
6	Current Term Days	88
7
8	1st Semester Days	89
9	1st Semester Days to Current	89
10	2nd Semester Days	88
11	2nd Semester Days to Current	85
12
13	Labor Day	2021-09-06
14	Retreat
15	Convention	2021-09-23
16	Convention	2021-09-24
17	Thanksgiving	2021-11-25
18	Thanksgiving	2021-11-26
19	Christmas	2021-12-20
20	Christmas	2021-12-21
21	Christmas	2021-12-22
22	Christmas	2021-12-23
23	Christmas	2021-12-24
24	Christmas	2021-12-27
25	Christmas	2021-12-28
26	Christmas	2021-12-29
27	Christmas	2021-12-30
28	Christmas	2021-12-31
29	President's Day	2022-02-21
30	Easter	2022-04-15
31	Easter	2022-04-18
32	Easter	2022-04-19
33	Easter	2022-04-20
34	Easter	2022-04-21
35	Easter	2022-04-22
Calendar

Cell Formulas
Range	Formula
D1	D1	=NETWORKDAYS(B1,B2,B13:B35)
B8	B8	=NETWORKDAYS(B1,B2,B13:B35)
B9	B9	=NETWORKDAYS(B1,MIN(TODAY(),B2),B13:B35)
B10	B10	=NETWORKDAYS(B2+1,B3,B13:B35)
B11	B11	=NETWORKDAYS(B2+1,MIN(TODAY(),B3),B13:B35)