Conditional Formating Help

[jmattingly85]

I need some help with conditional formatting. Below is a sample of my data:

	A	B	C
1	Current	7.92%
2			10%
3			9%
4			8%
5			7%
6			6%

I would like to add conditional formatting to Column C to highlight the cell (and three columns to the right) in Column C that cell B1 falls in between, rounding up to the nearest percent. So in this example, C4 would be highlighted. Is this possible?

 

[etaf]

how about
=$D2=INDEX($D$2:$D$6,MATCH(ROUNDUP($B$1,2),$D$2:$D$6,-1))
in conditional formatting

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
D2:G100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=$D2=INDEX($D$2:$D$6,MATCH(ROUNDUP($B$1,2),$D$2:$D$6,-1))

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

Book7
	A	B	C	D	E	F	G	H
1	Current	7.92%						lookup value returned
2	2			10.00%				0.08
3	3			9.00%
4	4			8.00%
5	5			7.00%
6	6			6.00%
Sheet1

Cell Formulas
Range	Formula
H2	H2	=INDEX($D$2:$D$6,MATCH(ROUNDUP($B$1,2),$D$2:$D$6,-1))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3:G6,D2:E2,G2	Expression	=$D2=INDEX($D$2:$D$6,MATCH(ROUNDUP($B$1,2),$D$2:$D$6,-1))	text	NO
H2	Expression	=$D2=INDEX($D$2:$D$6,MATCH(ROUNDUP($B$1,2),$D$2:$D$6,-1))	text	NO

 

[jmattingly85]

This mostly worked, however the roundup part isn't quite working. See below:

 

[etaf]

what formula did you use
works for me
=INDEX($K$7:$K$21,MATCH(ROUNDUP($J$5,2),$K$7:$K$21,-1))

NOTE the selection row - must be the same as the formula row
so
selection Applied to K7:K100
and
K7 in the formula - ROW 7 must match
=$K7=INDEX($K$7:$K$21,MATCH(ROUNDUP($J$5,2),$K$7:$K$21,-1))

whats the formula you are using and the applied to range


for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
K7:K100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=$K7=INDEX($K$7:$K$21,MATCH(ROUNDUP($J$5,2),$K$7:$K$21,-1))

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

Book6
	J	K	L	M
1
2
3
4				showing formula
5	7.91%			0.08
6
7		15%
8		14%
9		13%
10		12%
11		11%
12		10%
13		9%
14		8%
15		7%
16		6%
17		5%
18		4%
19		3%
20		2%
21		1%
Sheet3

Cell Formulas
Range	Formula
M5	M5	=INDEX($K$7:$K$21,MATCH(ROUNDUP($J$5,2),$K$7:$K$21,-1))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K7:K21	Expression	=$K7=INDEX($K$7:$K$21,MATCH(ROUNDUP($J$5,2),$K$7:$K$21,-1))	text	YES

 

[Alex Blakenburg]

If etaf's solution doesn't work for you then
• Is every percentage step represented in the list ?
If yes just something like this might work

=ROUND($K7,2)=ROUNDUP($J$5,2)

• If not and you just wanted the next largest number to match, you could try:

=XLOOKUP($J$5,$K$7:$K$21,$K$7:$K$21,-1,1)=$K7