% conditions for conditional formatting

[MSC]

Hi, I'm trying to create formatting rules for different groups of 5 or so numbers.
Any help is appreciated:

Green = any number in the top 1/3 of only the positive numbers - realizing that with some datasets, there may only be 1 or 2 positive numbers.
Mid Green = middle 1/3 of only the positive numbers
Light Green = bottom 1/3 of only the positive numbers

Light grey = actual number is between 0 and negative 5%
Dark grey = actual number is < 5%

The last 2 conditions seem pretty straighforward; feels like the first 3 conditions will need to be a formula, maybe using something related to percentiles.

Thank you.

PS this is Excel v2402, for Office 365.

 

[etaf]

how you define the top 1/3 , middle 3rd , bottom 3rd of positive numbers ?
I may be missing something obvious here
but if you have a list of numbers - for example
which are the top1/3 - Middle 1/3 bottom 1/3

can you give a sample example

Book1-ETAF.xlsx
	A
2	1
3	2
4	3
5	2
6	3
7	4
8	6
9	7
10	8
11	-1
12	-3
13	-2
Sheet1

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[MSC]

Thank you, I tried downloading XL2BB to my work PC, but it says 'type not supported in protected view' even when I had a new, blank sheet open.
So here is the raw data, thanks again for your help!

Note that my data comes in clusters of ~5 of so items.

A	1.0%
B	-1.2%
C	-0.4%
D	-2.0%
E	9.2%
F	4.0%
G	4.8%
Ford	1.9%
GM	2.0%
Nissan	3.0%
Honda	0.9%
Land Rover	0.8%
Coke	-1.7%
Pepsi	2.2%
Sprite	-7.9%
Ginger ale	3.9%
Fanta	1.2%

 

[MSC]

Ok, I posted 3 groups of data separated by carriage returns, but then after I hit Post Reply, they somehow got smushed together into one dataset.

Let me try again to put them into 3 groups:


FIRST GROUP

A	1.0%
B	-1.2%
C	-0.4%
D	-2.0%
E	9.2%
F	4.0%
G	4.8%
----------------------------------------------
- - - SECOND GROUP
Ford	1.9%
GM	2.0%
Nissan	3.0%
Honda	0.9%
Land Rover	0.8%
--------------------------------------------------
- - - THIRD GROUP
Coke	-1.7%
Pepsi	2.2%
Sprite	-7.9%
Ginger ale	3.9%
Fanta	1.2%

Thank you.

 

[etaf]

which would be coloured - can you show how that works

Green = any number in the top 1/3 of only the positive numbers - realizing that with some datasets, there may only be 1 or 2 positive numbers.
Mid Green = middle 1/3 of only the positive numbers
Light Green = bottom 1/3 of only the positive numbers

 

[MSC]

which would be coloured - can you show how that works

You raise a good question and I need to work out an answer with some folks in my group on that. Indeed, what if there's only 1 positive number, is that in the top 3rd, middle 3rd, or bottom 3rd? Arguably it's all 3......let me find out and re-post back here. Thanks.

 

[MSC]

We have finalized our criteria.

Now just looking to understand the keystrokes and formulas that I need. I'm figuring step 1 is Home, Conditional Formatting.

Green is any number >= 4%
Light green is any number > 2% and <4%
Light yellow is any number >= 0% and <=2%

Light grey is any number < 0% and > -5%
Dark grey is any number <= 5%

Thanks again, this is a big help to me.

 

[etaf]

ok
5 rules
Assuming thet Start in B2
You put the rules in a specific ORDER - you will see an Up/ Down Arrow to change the ORDER and then use STOP IF TRUE
or you need to use an AND()
you will also Need an AND any way to stop blank cells being highlighted and seen as 0

PUT the formulas in ths ORDER

IN this ORDER
Green is any number >= 4% - =AND(B2<>"",B2>=0.04)
Light green is any number > 2% and <4% - =AND(B2<>"",B2>0.02)
Light yellow is any number >= 0% and <=2% - =AND(B2<>"",B2>=0)

Dark grey is any number <= -5% - =AND(B2<>"",B2<=-0.05)
Light grey is any number < 0% and > -5% - =AND(B2<>"",B2<0)

OR to cover the range of values, so ORDER does not matter

Green is any number >= 4% - =AND(B2<>"",B2>=0.04)
Light green is any number > 2% and <4% - =AND(B2<>"",B2>0.02, B2<0.04)
Light yellow is any number >= 0% and <=2% - =AND(B2<>"",B2>=0, B2<=0.02)

Dark grey is any number <= -5% - =AND(B2<>"",B2<=-0.05)
Light grey is any number < 0% and > -5% - =AND(B2<>"",B2<0, B2>-0.05)


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

Highlight applicable range >>
B2:B100 - 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:
=AND(B2<>"",B2>=0.04)

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

NOW repeat for the other 4 formulas
PUT IN THE ORDER and use STOP IF TRUE

Book13
	A	B
1	CAR	Percent
2	A	1.00%
3	B	-1.20%
4	C	-0.40%
5	D	-2.00%
6	E	9.20%
7	F	4.00%
8	G	4.80%
9	Ford	1.90%
10	GM	2.00%
11	Nissan	3.00%
12	Honda	0.90%
13	Land Rover	0.80%
14	Coke	-1.70%
15	Pepsi	2.20%
16	Sprite	-7.90%
17	Ginger ale	3.90%
18	Fanta	1.20%
19
20
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B20	Expression	=AND(B2<>"",B2>=0.04)	text	YES
B2:B18	Expression	=AND(B2<>"",B2>0.02)	text	YES
B2:B20	Expression	=AND(B2<>"",B2>=0)	text	YES
B2:B20	Expression	=AND(B2<>"",B2<=-0.05)	text	YES
B2:B20	Expression	=AND(B2<>"",B2<0)	text	YES

i have also copied the file onto dropbox - will only be on the share for a few days

Dropbox

www.dropbox.com

you can see the order and stop if TRUE here

 

[MSC]

Etaf, that worked perfectly, thanks for your help!

 

[etaf]

you are welcome