Conditional Formatting Names Based on Their Position in a Column

[excelnewb2021]

Hello, I’m trying to conditionally format a column of names on the basis of where they appear in the range. I’m trying to break them up into four groups and am trying to use their relative position to allow for this. For example, out of 28 names, the individual who is in position number 2 should be formatted as blue. I’ve tried using the match and count function =match(value, range,0) <= count(range)*.25 to achieve this, but no luck so far. Any ideas?

 

[Radoslaw Poprawski]

Yes.
the individual who is in position number 2 <- Is this relative in a range or is it by spreadsheet row number (its important to distinguish it)
Then your formula must return TRUE or FALSE logical result.
Then go to CF and use formula to format cells <--- remember about relative cell references since this is extremly important here.

 

[excelnewb2021]

Thanks for the response, it is relative to the range of values. Ive tried using the formula to conditionally format, the thing is, the match function doesn’t let me refer to the lookup value dynamically… i would have to input the look up value individually for each cell, and that would be terribly time consuming

 

[Radoslaw Poprawski]

Then provide small data sample.
otherwise its a guessing game.

 

[excelnewb2021]

The goal is to highlight them on the basis of quartiles, so I’m trying to get excels conditional formatting to compare the relative position of a data point to the total number of entries in the data set. I figured using the match + count functions made sense as I can use a multiplier to create quartiles (count *.25, *.5 etc.)

name1​

name2​

name3​

name4​

name5​

name6​

name7​

name8​

name9​

name10​

name11​

name12​

name13​

name14​

name15​

name16​

name17​

name18​

name19​

name20​

name21​

name22​

name23​

name24​

name25​

name26​

name27​

name30​

name31​

name32​

 

[Radoslaw Poprawski]

will this be ok?

oAAQqn69R4ikZ1sZ6xst_work.csv
	A	B
1	Name	Column5
2	name1	TRUE
3	name2	TRUE
4	name3	TRUE
5	name4	TRUE
6	name5	FALSE
7	name6	FALSE
8	name7	FALSE
9	name8	FALSE
10	name9	TRUE
11	name10	TRUE
12	name11	TRUE
13	name12	TRUE
14	name13	FALSE
15	name14	FALSE
16	name15	FALSE
17	name16	FALSE
18	name17	TRUE
19	name18	TRUE
20	name19	TRUE
21	name20	TRUE
22	name21	FALSE
23	name22	FALSE
24	name23	FALSE
25	name24	FALSE
26	name25	TRUE
27	name26	TRUE
28	name27	TRUE
29	name30	TRUE
30	name31	FALSE
31	name32	FALSE
Sheet3

Cell Formulas
Range	Formula
B2:B31	B2	=MOD(ROUNDUP(ROWS(A$2:A2)/4,0),2)=1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B31	Expression	=MOD(ROUNDUP(ROWS(A$2:A2)/4;0);2)=1	text	NO

 

[excelnewb2021]

That's interesting, but I was looking for a way to highlight the top quarter (names 1 thru 8) blue, the second quarter (names 9 thru 16) green, the third quarter (names 17 -24) orange, and the fourth quarter (names 25 -32) red.

 

[Radoslaw Poprawski]

you said "quartiles" meaning 4th.

change then my 4 to 8. it should still work
Like this:

oAAQqn69R4ikZ1sZ6xst_work.csv
	A	B
1	Name	Column5
2	name1	TRUE
3	name2	TRUE
4	name3	TRUE
5	name4	TRUE
6	name5	TRUE
7	name6	TRUE
8	name7	TRUE
9	name8	TRUE
10	name9	FALSE
11	name10	FALSE
12	name11	FALSE
13	name12	FALSE
14	name13	FALSE
15	name14	FALSE
16	name15	FALSE
17	name16	FALSE
18	name17	TRUE
19	name18	TRUE
20	name19	TRUE
21	name20	TRUE
22	name21	TRUE
23	name22	TRUE
24	name23	TRUE
25	name24	TRUE
26	name25	FALSE
27	name26	FALSE
28	name27	FALSE
29	name30	FALSE
30	name31	FALSE
31	name32	FALSE
Sheet3

Cell Formulas
Range	Formula
B2:B31	B2	=MOD(ROUNDUP(ROWS(A$2:A2)/8,0),2)=1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B31	Expression	=MOD(ROUNDUP(ROWS(A$2:A2)/8;0);2)=1	text	NO