Conditional formatting

[MitchS]

Lets see how I can explain this...I'm not good with XL formulas at all! I have a XL sheet I use for horse racing. Lots of numerical data on this sheet. I export the data from MS Access onto an XL sheet. A guy helped me with a formula that separates the races so I have a header on top of each race. Once the races are separated with headers on top I then copy and paste into a fresh XL sheet away form the sheet with the seperator code.

Here's what I want to do. I want to automatically highlight the top 3 numerical values in each race on various columns.

Now I know how to get the TOP RANKED highlighted IF the whole XL sheet was just ONE RACE...BUT my XL sheet gets separated into may 10 races AND I also want the top 3 highlighted not just the top one.

IF the sheet was one race and I Just wanted the top ranking I could use this...

"Equal to" =MAX($A$1:$A$200)


That would give me the top ranking on the whole sheet BUT....

The problem is that each sheet is separated in maybe 10 races so there is no EXACT constant with the column numbers as they change slightly with I seperate the races.


My question then is it possible to highlight the top 3 numerical values on a XL sheet where the column numbers are not constant as they change slightly when I separate the races for day to day use. The reason this is, is because there is no constant on the number of horses in each race.

Make sense? Is this possible?

Mitch

 

[MitchS]

I'm dedicated to being here as long as it takes to learn how to implement this formatting..

I thought that if I used Peters formula, that would give me the 'N,O,P" grouping. If I can understand that grouping then I could use that formula to set up the other groupings with further conditions1,2,3 etc with different colors etc..? Is that correct? Seems to make sense to me?

What I've tried has failed. This is what I've done to try and get the "N,O,P" grouping in yellow.

1. Click on format, then conditional formatting

2. 1st drop-down set to "formula is"

Also is there a way to copy and paste into this formula window from word? Doesn't seem to let me. I have to manually type in each formula?

3. From Peters formula I made a few changes that I though would give me what I'm looking for from this grouping.

I typed in the formula window-

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> CellNr.: / ConditionFormatN1. / Formula is =AND(N1>=LARGE(IF($B$1:$B$150=$B1,N$1:N$150),3),N1<>"")


Then I hit format. Then I made the color yellow. then OK


Formula doesn't work? I'm off somewhere?


Trying to use the ",N,O,P" grouping from column 1 through 150, color yellow.


I'll be here all day. Need some advise to get to the next step before I can continue.


Thanks ahead of time for any help from anyone,
Mitch

 

[Peter_SSs]

Here's an example of what I want do. Maybe you can point me in the right direction-

What follows the above is quite a wordy description which is somewhat difficult to follow given that we are not that familiar with your sheet. Since you have the HTML Maker working, why not illustrate that description with a screen shot?

You could set up a dummy sheet with say 2 races with 7 horses in each. You can colour the cells manually to show what results you want and you can hide columns that are not relevant to help the screen shot fairly small. Make sure any written description/explanation refers to the data in the screen shot.

Also is there a way to copy and paste into this formula window from word? Doesn't seem to let me. I have to manually type in each formula?

Why are you working in Word in the first place?

 

[MitchS]

OK, that's a good idea. I'll set up a dummy sheet with two races and manually fill in the colors. I'm on it

As far as why I'm working in word, well...what should I be working in?

 

[Peter_SSs]

As far as why I'm working in word, well...what should I be working in?

When I'm composing a MrExcel post I just compose it directly in the Quick Reply window. If I want to post a formula (without the HTML Maker which will do the formulas automatically) I copy it from the formula bar in Excel and paste directly into my post.

 

[MitchS]

I changed my mind and simplified the process.

1. Use same color
2. Highlight the top 2 HIGHEST in columns- H,K,L,N,O,Q,U,V,X
3. Highlight the top 2 LOWEST in columns- R,AA,AB

Excel Workbook
	H	I	J	K	L	M	N	O	P	Q	R
1	W-fit	P	CLS	E1	E2	Q/S	SR-L	SR-L9	SR-9A	Late	Kic
2	76	-15	4	72	71	7 - EP	87	86	82	92	4.8
3	82	-15	0	71	72	3 - NA	86	85	79	89	8.7
4	77	0	0	76	47	0 - NA	83	84	81	104	5.3
5	74	0	0	47	51	8 - E	100	79	70	93	3.8
6	84	-15	0	70	62	5 - EP	61	76	44	34	13.8
7
8	W-fit	P	CLS	E1	E2	Q/S	SR-L	SR-L9	SR-9A	Late	Kic
9	84	25	50	88	88	3 - E/P	108	104	101	107	7.1
10	98	3	66	85	85	1 - S	102	96	107	105	6.5
11	86	16	79	84	73	2 - P	97	94	105	138	3
12	77	13	52	89	87	3 - E/P	103	0	102	82	-3
13	89	-210	72	85	83	0 - S	80	0	103	110	2.6
14	82	25	45	65	69	0 - S	98	0	94	125	8.9
15
DED1

Excel 2003

Excel Workbook
	S	T	U	V	W	X	Y	Z	AA	AB
1	W%	ITM	J%	T%	WD	Comp	R-fit	ML	V-L9	V-L
2	0	0	7	13	1	108	105	4.5	2.6	6
3	0	0	19	0	-2	108	102	3	3.2	8.6
4	0	0	12	9	0	92	105	15	4.4	11
5	0	0	20	5	1	88	53	20	5.7	5
6	0	0	12	19	1	77	54	15	9.2	3
7
8	W%	ITM	J%	T%	WD	Comp	R-fit	ML	V-L9	V-L
9	33	33	12	16	0	128	91	5	1.6	4.3
10	25	50	12	18	1	133	90	3	2.1	5.3
11	50	100	11	22	-1	132	92	6	2.8	16
12	25	50	2	8	7	130	89	12	0	6.9
13	16	66	17	17	2	130	92	4.5	0	4.8
14	50	50	19	6	-2	114	93	10	0	8.7
15
DED1

Excel 2003

 

[MitchS]

Columns...A,B,C,D,E,F,G do not need any highlighting. The numbers will always start with 2 at the top and run through about 150 because there are 10 races generally.

Below is the beginning of the sheet. Put the 3 sheets together and that is one complete sheet.

Excel Workbook
	A	B	C	D	E	F	G
1	TK	RN	Horse	PN	Age	LR	WA
2	Ded	1	Ay Dios Mio	5	2	36	5
3	Ded	1	Captainwoodrowcall	6	2	36	82
4	Ded	1	Who Dat Ghost	12	2	35	13
5	Ded	1	Phone Speed	11	2	35	13
6	Ded	1	Doc R U It	3	2	48	7
7
8	TK	RN	Horse	PN	Age	LR	WA
9	Ded	2	Caddo Native	3	2	21	5
10	Ded	2	Watch My Smoke	5	2	27	55
11	Ded	2	Man Of Strife	6	2	57	5
12	Ded	2	Z Native	7	2	17	26
13	Ded	2	Jump Jump	1	2	47	13
14	Ded	2	K One Four	8	2	28	2
DED1

Excel 2003

 

[Peter_SSs]

Here is one way. I have used row 150 as the maximum. If you might need more than that you would need to allow for that in my descriptions below and in the formulas referencing row 150.

1. Clear any existing Conditional Formatting from the sheet, or start a new sheet with just the raw data. I suggest using the sample data you gave above to start with so you can compare to my results below.

2. Select H1:H150. You can do this by dragging down the column or by typing H1:H150 in the 'Name Box' (the white box to the left of the formula bar) and pressing Enter.

3. In Conditional Formatting use 'Formula is' with this formula:

=AND(H1>=LARGE(IF($B$1:$B$150=$B1,H$1:H$150),2),H1<>"")
and apply your colour. OK and OK to exit out of the CF dialog.

4. With H1:H150 still selected, double click the 'Format Painter' (paint brush icon in the toolbar).

5. Now carefully click the following cells only: K1, L1 ,N1, O1, Q1, U1, V1, X1

6. Before clicking anywhere else, click the Format Painter once more to de-activate further 'painting' of this format.

7. Select R1:R150 and use this formula in Conditional Formatting:

=AND(R1<=SMALL(IF($B$1:$B$150=$B1,R$1:R$150),2),R1<>"")
and set you colour (can be a different colour if you want).

8. With R1:R150 still selected use the Format Painter as above to apply the Format to columns AA and AB.

9. Remember to de-activate the Format Painter again.


I believe that will do what you have shown above with 2 exceptions which I have described below and highlighted blue in my screen shot:

a) I think this was just a mistake when you were applying colour manually but you have cell V4 highlighted when V2 is a larger number.

b) This one may require a little more work. In column AA for race 2 yo have 1.6 and 2.1 highlighted as the 2 lowest numbers. However, there are 3 zeros in that column/race and of course 0 is lower than the 2 numbers you have highlighted so my code highlights the 0s instead. What is the circumstance there?

Excel Workbook
	A	B	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	TK	RN	WA	W-fit	P	CLS	E1	E2	Q/S	SR-L	SR-L9	SR-9A	Late	Kic	W%	ITM	J%	T%	WD	Comp	R-fit	ML	V-L9	V-L
2	Ded	1	5	76	-15	4	72	71	7 - EP	87	86	82	92	4.8	0	0	7	13	1	108	105	4.5	2.6	6
3	Ded	1	82	82	-15	0	71	72	3 - NA	86	85	79	89	8.7	0	0	19	0	-2	108	102	3	3.2	8.6
4	Ded	1	13	77	0	0	76	47	0 - NA	83	84	81	104	5.3	0	0	12	9	0	92	105	15	4.4	11
5	Ded	1	13	74	0	0	47	51	8 - E	100	79	70	93	3.8	0	0	20	5	1	88	53	20	5.7	5
6	Ded	1	7	84	-15	0	70	62	5 - EP	61	76	44	34	13.8	0	0	12	19	1	77	54	15	9.2	3
7
8	TK	RN	WA	W-fit	P	CLS	E1	E2	Q/S	SR-L	SR-L9	SR-9A	Late	Kic	W%	ITM	J%	T%	WD	Comp	R-fit	ML	V-L9	V-L
9	Ded	2	5	84	25	50	88	88	3 - E/P	108	104	101	107	7.1	33	33	12	16	0	128	91	5	1.6	4.3
10	Ded	2	55	98	3	66	85	85	1 - S	102	96	107	105	6.5	25	50	12	18	1	133	90	3	2.1	5.3
11	Ded	2	5	86	16	79	84	73	2 - P	97	94	105	138	3	50	100	11	22	-1	132	92	6	2.8	16
12	Ded	2	26	77	13	52	89	87	3 - E/P	103	0	102	82	-3	25	50	2	8	7	130	89	12	0	6.9
13	Ded	2	13	89	-210	72	85	83	0 - S	80	0	103	110	2.6	16	66	17	17	2	130	92	4.5	0	4.8
14	Ded	2	2	82	25	45	65	69	0 - S	98	0	94	125	8.9	50	50	19	6	-2	114	93	10	0	8.7
Cf within race (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H1	1. / Formula is =AND(H1>=LARGE(IF($B$1:$B$150=$B1,H$1:H$150),2),H1<>"")	Abc
R1	1. / Formula is =AND(R1<=SMALL(IF($B$1:$B$150=$B1,R$1:R$150),2),R1<>"")	Abc

 

[MitchS]

I believe that will do what you have shown above with 2 exceptions which I have described below and highlighted blue in my screen shot:

a) I think this was just a mistake when you were applying colour manually but you have cell V4 highlighted when V2 is a larger number.

b) This one may require a little more work. In column AA for race 2 yo have 1.6 and 2.1 highlighted as the 2 lowest numbers. However, there are 3 zeros in that column/race and of course 0 is lower than the 2 numbers you have highlighted so my code highlights the 0s instead. What is the circumstance there?

Yes, "a" is my mistake and "b" I've decided not to highlight that column because a "0" should not be highlighted. Its really a N/A not a 0. That's fine, no big deal..

Thank you. I actually did it! And learned a bit about XL along the way

I'm going to save the sheet. Delete the data and then bring new data to the sheet and see what happens. Be back to let you know.

 

[MitchS]

Works great! ... I'm curious though. Is it possible without too much trouble to bypass the "0" as the lowest. In other words when a "0" shows up on the sheet the formula will not highlight the "0" ?

 

[Peter_SSs]

Works great! ... I'm curious though. Is it possible without too much trouble to bypass the "0" as the lowest. In other words when a "0" shows up on the sheet the formula will not highlight the "0" ?

It is possible, but it would add yet another level to the already resonably complex and resource-heavy Conditional Formatting. You may note that the sheet is already becoming a little slow to respond?

Would you consider this option? Select all the relevant columns (eg select column R by clicking its heading label then hold Ctrl while clicking, say, the column AA and AB labels) then ..

Edit|Replace...|Find what: 0 Replace with: N/A |Options>>|check 'Match entire cell contents|Replace All|OK|Close

If you do want the CF option, please ..

1. Confirm whether vto ignore zero values only applies to columns R, AA and AB or whether it is also to apply to the 9 'LARGE' columns as well.

2. Confirm that if these numbers
7
4
2
0
-3
5

appeared in column V then you would want to ignore the 0 and highlight -3 and 2.