Conditional formatting - with a formula

[adambc]

I have a list of values in a column that is added to every other day ...

Using =AVERAGE(SMALL((OFFSET(J4,COUNT(J:J),0,-20)),ROW(1:8))) I get the AVERAGE of the lowest 8 values in the latest 20 values ...

And if I use =SMALL((OFFSET(J4,COUNT(J:J),0,-20)),ROW(1:8)) I get an array of those 8 values ...

But how can I fill (eg in yellow) the cells containing those 8 values?

If I put =SMALL((OFFSET(J4,COUNT(J:J),0,-20)),ROW(1:8)) into a Conditional Formatting rule, it fills the cells containing the first 10 values of the latest 20 values!!!

I must be missing something?

Thanks in advance ...

 

[JamesCanale]

I needed a helper cell for this. Whenever I tried to shove the formula in K2 into the main formula, it caused an excel problem. Also, if the eighth lowest value appears twice, you'll get 9 yellow cells.

MrExcelPlayground5.xlsx
	J	K
1
2		31
3
4	10
5	55
6	59
7	38
8	41
9	26
10	70
11	73
12	74
13	82
14	85
15	39
16	24
17	72
18	64
19	73
20	78
21	40
22	87
23	74
24	56
25	17
26	38
27	63
28	89
29	16
30	15
31	44
32	91
33	70
34	40
35	31
36	23
37	16
38	54
39	90
40	30
41	83
42	14
43	99
44	55
45	13
Sheet22

Cell Formulas
Range	Formula
K2	K2	=SMALL(OFFSET(J4,(ROW($J$4)+COUNT(J:J)-20)-ROW(J4),0,20,1),8)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J4:J52	Expression	=SUMPRODUCT(--(ROW(J4)>=(ROW($J$4)+COUNT(J:J)-20)),--(J4<>""),--(J4<=$K$2))	text	NO

 

[adambc]

I needed a helper cell for this. Whenever I tried to shove the formula in K2 into the main formula, it caused an excel problem. Also, if the eighth lowest value appears twice, you'll get 9 yellow cells.

MrExcelPlayground5.xlsx
	J	K
1
2		31
3
4	10
5	55
6	59
7	38
8	41
9	26
10	70
11	73
12	74
13	82
14	85
15	39
16	24
17	72
18	64
19	73
20	78
21	40
22	87
23	74
24	56
25	17
26	38
27	63
28	89
29	16
30	15
31	44
32	91
33	70
34	40
35	31
36	23
37	16
38	54
39	90
40	30
41	83
42	14
43	99
44	55
45	13
Sheet22

Cell Formulas
Range	Formula
K2	K2	=SMALL(OFFSET(J4,(ROW($J$4)+COUNT(J:J)-20)-ROW(J4),0,20,1),8)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J4:J52	Expression	=SUMPRODUCT(--(ROW(J4)>=(ROW($J$4)+COUNT(J:J)-20)),--(J4<>""),--(J4<=$K$2))	text	NO

@JamesCanale

Have tried, but failed, to implement this - but in all honesty, if it's that complex, I can do without an automatic way to fill the cells containing "the lowest 8 values in the latest 20 values" ...

But thanks for replying ...

 

[adambc]

I needed a helper cell for this. Whenever I tried to shove the formula in K2 into the main formula, it caused an excel problem. Also, if the eighth lowest value appears twice, you'll get 9 yellow cells.

MrExcelPlayground5.xlsx
	J	K
1
2		31
3
4	10
5	55
6	59
7	38
8	41
9	26
10	70
11	73
12	74
13	82
14	85
15	39
16	24
17	72
18	64
19	73
20	78
21	40
22	87
23	74
24	56
25	17
26	38
27	63
28	89
29	16
30	15
31	44
32	91
33	70
34	40
35	31
36	23
37	16
38	54
39	90
40	30
41	83
42	14
43	99
44	55
45	13
Sheet22

Cell Formulas
Range	Formula
K2	K2	=SMALL(OFFSET(J4,(ROW($J$4)+COUNT(J:J)-20)-ROW(J4),0,20,1),8)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J4:J52	Expression	=SUMPRODUCT(--(ROW(J4)>=(ROW($J$4)+COUNT(J:J)-20)),--(J4<>""),--(J4<=$K$2))	text	NO

@JamesCanale

OK, got it working (my list was in a table so had to modify your formula for the helper cell) ...

But you're right, if there are more values = to the eighth lowest value (which there are and likely to always be) those cells are filled too ...

Not to worry, I tried (and with your help almost got there), but I can live without it ...

Thanks again ...