Conditional Formatting and Percentages

[Rosstamon]

Hi All,
I have a spreadsheet which, in column L there is a percentage that is based on the numbers in columns D and G.

I would like to create a conditional format that says:
-if column L is less than or equal to 3% format the entire row green
-if column L is between 4 and 6% format the entire row blue
-if column L is greater than or equal to 7% format the entire row red
Currently there are 379 rows and about 63 rows a day will be added to the spreadsheet indefinitely.

I don't know how to do this. I know it sounds like it should be easy but I can't figure it out.

Any help would be appreciated.

Thank you,
Rosstamon

 

[etaf]

select all the columns you want to colour
Then setup 3 rules in conditional formatting

Also make sure they are put in this Order

RULE
=AND(ISNUMBER($L1),$L1 > = 0.07)
Format RED
stop if True

RULE
=AND(ISNUMBER($L1),$L1 > 0.03)
Format Blue
stop if True

RULE
=AND($L1<>"", $L1< = 0.03, ISNUMBER($L1))
Format Green
STOP IF TRUE

What do you want to do with fractions
I have assumed included
so anything greater than 3% and less than 7% will be blue

the $ fixes the Cell column L and so will highlight the row , based on the columns you selected at the beginning of each rule

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1												Percent
2												10%
3												7%
4												2%
5												4%
6												5%
7												2%
8												3%
9												8%
10
11
12
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:L	Expression	=AND(ISNUMBER($L1),$L1>=0.07)	text	YES
A:L	Expression	=AND(ISNUMBER($L1),$L1 > 0.03)	text	NO
A:L	Expression	=AND( $L1<>"",$L1<=0.03,ISNUMBER($L1))	text	NO

 

[Rosstamon]

I appreciate the attempt but it didn't work for me. Also, in case it helps I'm on a Mac with Mojave and using Microsoft 365. I noticed you are also on a Mac with 365. I'm attaching a snap shot of my rules and a partial of my spreadsheet. I'm downloading daily Covid numbers and tracking results. Trying to get a real world understanding of what's happening.

 

[Fluff]

As the applies to range starts on row 2, you need to change L1 to L2.
Also your first rule has " around it, they need to be removed.

 

[etaf]

Edit posted at same time as fluff
Not sure why you are not seeing blue on 1st rows L2 , unless the " in the first rule is causing an issue

Yes i'm on a mac , microsoft 365 , they changed the name now no idea why, everyone knows office for decades
on latest OSX

looking at the screen shots
you have used column L as the criteria
Also rule 1 has " round it - so those need to be removed
the range you selected was C2 to L442
then the conditional format using L1 will be 1 row out
so as you have selected a range , rather than the entire column
change the formulas from L1 to L2

are the numbers in L actual % numbers or TEXT text
change the format of L to number
and you should see

0.05
0.04

 

[Rosstamon]

Many MANY thanks to you. I changed my range to full columns, the format of Column L to numbers from percentage, and copied and pasted your formulas and it works now. See below.
The weird thing, I originally copied and pasted your formulas but it would not let me use the equal sign (=) so I removed that and it added the equal sign and the quotation marks. This time when I copied and pasted your formulas after making those other changes it worked. Must have had something to do with that. Thank you very much for sticking with me on this.

 

[etaf]

it should still work OK formatted to % as shown on my example
I just suggested the change to numbers to check they where the correct numbers and not text

 

[Rosstamon]

Just tried it and you're correct, it also works when formatted to %. I suspect whatever it is I did wrong the first time around, perhaps selecting a range rather than the entire columns $C:$L is what caused the issue. While I was doing this I noticed that some of the lines were the wrong colors so I changed the values as follows and now it seems to work.
And since the last formula is cut off it looks like this:
=AND( $L1<>"",$L1<=0.03499,ISNUMBER($L1))

Again, thank you for your help.

 

[etaf]

did you want less than 3,5%
use < 0.035
otherwise 0.034999 would not be included
not much of an issue but i prefer not to use loads of decimals places unless specifically needed

 

[Rosstamon]

did you want less than 3,5%
use < 0.035
otherwise 0.034999 would not be included
not much of an issue but i prefer not to use loads of decimals places unless specifically needed

I was trying to achieve the following:

When the result in column L was between
0 - 3%, color that full row Green
4 - 6%, color that full row Blue
7 - 9%, color that full row Red

The result of the conditional formatting was:

When the result in column L was between
0 - 3%, color that full row Green
3 - 7%, color that full row Blue
7 - 9%, color that full row Red

It turns out the percentages are rounded up to a whole number, so I altered the formulas but when the percentages are carried out to multiple decimal places, it reveals that some of the 3% whole numbers are closer to 4%, so those rows are colored blue. For this reason I used longer decimal places in the formulas and it seems to solve the problem.

So now I get this: