Conditional formatting

[Ywkni]

Hi,

Hoping someone can help, as my brain is a little frazzled and I’m not very maths clever! I have just (about) taught myself the basics of conditional formatting for >/=, but I’m really struggling to demonstrate lower than via a certain percentage.

I have an expected value in one cell and actual value in a second cell. In a separate cell, I want the cell to highlight red if the actual is 20% or more behind the the expected number and amber if the actual number is between between 1-19% behind.

I really hope that makes sense! Would really appreciate any help

 

[Jeffrey Mahoney]

Let's say cell A1 is your expected value, cell B1 is the actual. The CF formula would be: =1-($B1/$A1)>=0.2

 

[Ywkni]

Let's say cell A1 is your expected value, cell B1 is the actual. The CF formula would be: =1-($B1/$A1)>=0.2

Thank you for your help Jeffrey That’s working for the my 20% theory. Is there a formula for the between 1-19% behind actual?

 

[DanteAmor]

Hi and welcome to MrExcel.

Try:

varios 23nov2023.xlsm
	A	B	C	D	E
1		EXPECTED	REAL	CF	Ex
2		100	80		-20%
3		100	75		-25%
4		100	110		10%
5		100	86		-14%
6		100	99		-1%
7		100	90		-10%
8		100	81		-19%
9		100	102		0%
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D9	Expression	=(C2-B2)/100<=-0.2	text	NO
D2:D9	Expression	=AND((C2-B2)/100<=-0.01,(C2-B2)/100>=-0.19)	text	NO

 

[Ywkni]

Hi and welcome to MrExcel.

Try:

varios 23nov2023.xlsm
	A	B	C	D	E
1		EXPECTED	REAL	CF	Ex
2		100	80		-20%
3		100	75		-25%
4		100	110		10%
5		100	86		-14%
6		100	99		-1%
7		100	90		-10%
8		100	81		-19%
9		100	102		0%
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D9	Expression	=(C2-B2)/100<=-0.2	text	NO
D2:D9	Expression	=AND((C2-B2)/100<=-0.01,(C2-B2)/100>=-0.19)	text	NO

Hi,

Thank you for you’re help, you have made my day a lot easier