Excel formula question, MAX?

[Capper2824]

Hi all, this is my first question so please be kind lol.

I have an excel formula question.

How can I write a formula for a league where I want to get the formula to highlight the maximum scorer in the month.

The maximum scorers in this example are 2 people both achieving 17 points each.

On the second month I have a person who scored 16 (this is highlighted in colour Cyan).

What I need is for the highest scorer in each month and in this example anyone beating 17 should cancel out the previous colour of Cyan

Within this first month (and consecutive months), I have a Monthly Total and an Overall Total, as per the example these are working fine, and I do not want these affected by the highest eekkly scorer total.

hope this make some sort of sense to somebody, please help.


Thank you in anticipation for any help.

KInd regards.

Robert

 

[Capper2824]

Hi again, i have sent something to the message board but not sure where it has gone. i think i have sent it via xl2bb?

 

[Capper2824]

mrexcel example.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	07/08/2021	14/08/2021	17/08/2021	21/08/2021	28/09/2021				TOTAL		04/09/2021	11/09/2021	14/09/2021	18/09/2021					TOTAL
3	R1	R2	R3	R4	R5			MT	OT		R6	R7	R8	R9	R10			MT	OT
4			MONTH 1										MONTH 2
5	7	8	9	9	7			40	40		7	8	6	9				30	70
6	4	11	5	12	4			36	36		4	12	7	9				32	68
7	7	8	0	0	5			20	20		0	0	0	4				4	24
8	4	11	0	4	4			23	23		10	9	8	7				34	57
9	12	16	11	11	4			54	54		7	8	9	10				34	88
10	2	11	2	6	4			25	25		9	8	8	6				31	56
11	2	11	15	4	5			37	37		9	10	11	7				37	74
12	4	8	5	9	5			31	31		2	11	10	2				25	56
13	4	8	2	2	6			22	22		0	6	8	9				23	45
14	7	9	2	8	9			35	35		7	10	6	4				27	62
15	12	8	9	6	7			42	42		7	6	12	6				31	73
16	7	9	0	9	7			32	32		0	4	0	7				11	43
17	2	17	5	0	2			26	26		0	4	8	7				19	45
18	12	9	7	4	2			34	34		0	14	6	4				24	58
19	9	11	5	13	9			47	47		0	9	6	6				21	68
20	2	8	11	7	4			32	32		2	6	4	9				21	53
21	5	4	2	8	6			25	25		7	6	11	12				36	61
22	12	13	0	10	7			42	42		2	13	6	7				28	70
23	4	8	6	12	10			40	40		7	6	11	6				30	70
24	4	11	0	4	2			21	21		4	6	6	9				25	46
25	4	6	4	9	10			33	33		0	10	0	9				19	52
26	4	11	2	8	2			27	27		10	4	4	4				22	49
27	12	9	2	11	6			40	40		7	6	8	9				30	70
28	2	8	2	14	12			38	38		5	6	2	4				17	55
29	4	9	4	6	4			27	27		2	8	8	7				25	52
30	7	10	5	11	2			35	35		7	4	9	2				22	57
31	7	11	4	4	7			33	33		2	8	11	8				29	62
32	9	11	0	11	6			37	37		5	6	6	9				26	63
33	2	9	0	4	9			24	24		2	9	0	11				22	46
34	4	11	2	13	4			34	34		10	9	6	12				37	71
35	9	10	7	14	7			47	47		7	9	6	9				31	78
36	12	6	7	6	2			33	33		2	6	8	11				27	60
37	5	8	4	6	0			23	23		2	6	13	8				29	52
38	6	8	2	9	12			37	37		2	8	11	12				33	70
39	7	11	8	11	12			49	49		0	11	4	4				19	68
40	7	8	7	4	12			38	38		0	6	8	4				18	56
41	7	11	7	11	7			43	43		2	8	13	9				32	75
42	2	8	4	8	4			26	26		4	11	6	9				30	56
43	4	13	4	12	2			35	35		2	6	11	0				19	54
44	2	6	0	4	0			12	12		7	0	6	9				22	34
45	9	11	2	9	2			33	33		7	11	9	11				38	71
46	9	6	7	2	4			28	28		2	4	9	13				28	56
47	0	11	7	13	4			35	35		7	4	6	4				21	56
48	7	11	10	8	2			38	38		5	9	6	7				27	65
49	2	11	7	8	9			37	37		7	6	8	6				27	64
50	4	8	2	8	2			24	24		2	8	0	6				16	40
51	14	8	4	0	8			34	34		4	16	11	7				38	72
52	2	6	7	7	5			27	27		7	8	4	6				25	52
53	7	9	2	17	9			44	44		0	6	9	9				24	68
54	4	13	7	12	9			45	45		11	6	7	4				28	73
55	2	8	0	13	5			28	28		5	8	6	9				28	56
56	0	11	2	6	7			26	26		0	11	6	4				21	47
57	6	6	7	11	2			32	32		0	9	12	2				23	55
58	2	11	7	6	7			33	33		0	13	6	6				25	58
59	2	8	7	6	2			25	25		7	8	8	14				37	62
60	10	2	7	4	2			25	25		10	9	7	12				38	63
61	2	11	7	8	5			33	33		4	6	11	7				28	61
62	9	13	6	14	10			52	52		2	2	7	4				15	67
63	2	11	0	6	4			23	23		2	10	0	0				12	35
64	4	10	6	4	9			33	33		2	10	0	12				24	57
65	2	8	0	11	7			28	28		4	8	8	4				24	52
66	2	8	2	4	7			23	23		7	11	9	11				38	61
67	7	6	4	8	11			36	36		2	10	4	8				24	60
68	12	11	5	6	7			41	41		7	6	11	2				26	67
69	12	11	7	9	9			48	48		2	6	11	11				30	78
70	6	8	14	9	9			46	46		7	11	13	6				37	83
Sheet1

 

[Capper2824]

i do hope i have done this right lol? thanks again.

Rob.

 

[JGordon11]

Select Range A5:S70, Home, Conditional Formatting, New rule, Use a formula to determine which cells to format,

Put this formula in the rule description box: =AND(A5<>0,A5=MAX(A$5:A$70))

click Format..., Fill, pick your color, click Ok , click Apply, click Ok

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
5	7	8	9	9	7			40	40		7	8	6	9				30	70
6	4	11	5	12	4			36	36		4	12	7	9				32	68
7	7	8	0	0	5			20	20		0	0	0	4				4	24
8	4	11	0	4	4			23	23		10	9	8	7				34	57
9	12	16	11	11	4			54	54		7	8	9	10				34	88
10	2	11	2	6	4			25	25		9	8	8	6				31	56
11	2	11	15	4	5			37	37		9	10	11	7				37	74
12	4	8	5	9	5			31	31		2	11	10	2				25	56
13	4	8	2	2	6			22	22		0	6	8	9				23	45
14	7	9	2	8	9			35	35		7	10	6	4				27	62
15	12	8	9	6	7			42	42		7	6	12	6				31	73
16	7	9	0	9	7			32	32		0	4	0	7				11	43
17	2	17	5	0	2			26	26		0	4	8	7				19	45
18	12	9	7	4	2			34	34		0	14	6	4				24	58
19	9	11	5	13	9			47	47		0	9	6	6				21	68
20	2	8	11	7	4			32	32		2	6	4	9				21	53
21	5	4	2	8	6			25	25		7	6	11	12				36	61
22	12	13	0	10	7			42	42		2	13	6	7				28	70
23	4	8	6	12	10			40	40		7	6	11	6				30	70
24	4	11	0	4	2			21	21		4	6	6	9				25	46
25	4	6	4	9	10			33	33		0	10	0	9				19	52
26	4	11	2	8	2			27	27		10	4	4	4				22	49
27	12	9	2	11	6			40	40		7	6	8	9				30	70
28	2	8	2	14	12			38	38		5	6	2	4				17	55
29	4	9	4	6	4			27	27		2	8	8	7				25	52
30	7	10	5	11	2			35	35		7	4	9	2				22	57
31	7	11	4	4	7			33	33		2	8	11	8				29	62
32	9	11	0	11	6			37	37		5	6	6	9				26	63
33	2	9	0	4	9			24	24		2	9	0	11				22	46
34	4	11	2	13	4			34	34		10	9	6	12				37	71
35	9	10	7	14	7			47	47		7	9	6	9				31	78
36	12	6	7	6	2			33	33		2	6	8	11				27	60
37	5	8	4	6	0			23	23		2	6	13	8				29	52
38	6	8	2	9	12			37	37		2	8	11	12				33	70
39	7	11	8	11	12			49	49		0	11	4	4				19	68
40	7	8	7	4	12			38	38		0	6	8	4				18	56
41	7	11	7	11	7			43	43		2	8	13	9				32	75
42	2	8	4	8	4			26	26		4	11	6	9				30	56
43	4	13	4	12	2			35	35		2	6	11	0				19	54
44	2	6	0	4	0			12	12		7	0	6	9				22	34
45	9	11	2	9	2			33	33		7	11	9	11				38	71
46	9	6	7	2	4			28	28		2	4	9	13				28	56
47	0	11	7	13	4			35	35		7	4	6	4				21	56
48	7	11	10	8	2			38	38		5	9	6	7				27	65
49	2	11	7	8	9			37	37		7	6	8	6				27	64
50	4	8	2	8	2			24	24		2	8	0	6				16	40
51	14	8	4	0	8			34	34		4	16	11	7				38	72
52	2	6	7	7	5			27	27		7	8	4	6				25	52
53	7	9	2	17	9			44	44		0	6	9	9				24	68
54	4	13	7	12	9			45	45		11	6	7	4				28	73
55	2	8	0	13	5			28	28		5	8	6	9				28	56
56	0	11	2	6	7			26	26		0	11	6	4				21	47
57	6	6	7	11	2			32	32		0	9	12	2				23	55
58	2	11	7	6	7			33	33		0	13	6	6				25	58
59	2	8	7	6	2			25	25		7	8	8	14				37	62
60	10	2	7	4	2			25	25		10	9	7	12				38	63
61	2	11	7	8	5			33	33		4	6	11	7				28	61
62	9	13	6	14	10			52	52		2	2	7	4				15	67
63	2	11	0	6	4			23	23		2	10	0	0				12	35
64	4	10	6	4	9			33	33		2	10	0	12				24	57
65	2	8	0	11	7			28	28		4	8	8	4				24	52
66	2	8	2	4	7			23	23		7	11	9	11				38	61
67	7	6	4	8	11			36	36		2	10	4	8				24	60
68	12	11	5	6	7			41	41		7	6	11	2				26	67
69	12	11	7	9	9			48	48		2	6	11	11				30	78
70	6	8	14	9	9			46	46		7	11	13	6				37	83
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5:S70	Expression	=AND(A5<>0,A5=MAX(A$5:A$70))	text	NO

 

[Capper2824]

Thank you for your input jgordon11 but this is filling cells with The same colour, unfortunately this would not be an appropriate formula for me, but thank you for your help.

 

[Capper2824]

Hi again all.

Thank you again for your input thus far, but could still do with a solution to this problem, if anyone can help.

I need some sort of formula that will highlight in Cyan the highest single scorer in any given month (there are 10 months in all).

At the moment the highest single scorer has 17, if this gets equalled as it has then 17 still gets highlighted, but if beaten then I need all highlighted to go to normal (no highlight).

But anything beating this score, at the moment (17) then I need these to be highlighted.

I also have a formula for Monthly Total (MT in orange) and Overall Total (OT in yellow), these cells I do not want to include as these are working fine.

Hope there is a solution out there if anyone could help please.

Thank you.

Rob.

 

[Peter_SSs]

I need some sort of formula that will highlight in Cyan the highest single scorer in any given month

See if something like this would help. You will need to adjust the ranges to cover your 10 (or future 12?) months.
I have hidden quite a few rows to make this mini-sheet a bit smaller.

21 09 28.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	7/08/2021	14/08/2021	17/08/2021	21/08/2021	28/09/2021				TOTAL		4/09/2021	11/09/2021	14/09/2021	18/09/2021					TOTAL
3	R1	R2	R3	R4	R5			MT	OT		R6	R7	R8	R9	R10			MT	OT
4			MONTH 1										MONTH 2
5	7	8	9	9	7			40	40		7	8	6	9				30	70
6	4	11	5	12	4			36	36		4	12	7	9				32	68
14	7	9	2	8	9			35	35		7	10	6	4				27	62
15	12	8	9	6	7			42	42		7	6	12	6				31	73
16	7	9	0	9	7			32	32		0	4	0	7				11	43
17	2	17	5	0	2			26	26		0	4	8	7				19	45
18	12	9	7	4	2			34	34		0	14	6	4				24	58
19	9	11	5	13	9			47	47		0	9	6	6				21	68
49	2	11	7	8	9			37	37		7	6	8	6				27	64
50	4	8	2	8	2			24	24		2	8	0	6				16	40
51	14	8	4	0	8			34	34		4	16	11	7				38	72
52	2	6	7	7	5			27	27		7	8	4	6				25	52
53	7	9	2	17	9			44	44		0	6	9	9				24	68
54	4	13	7	12	9			45	45		11	6	7	4				28	73
55	2	8	0	13	5			28	28		5	8	6	9				28	56
68	12	11	5	6	7			41	41		7	6	11	2				26	67
69	12	11	7	9	9			48	48		2	6	11	11				30	78
70	6	8	14	9	9			46	46		7	11	13	6				37	83
CF Highest

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5:S70	Expression	=A5=AGGREGATE(14,6,$A$5:$S$70/((MONTH($A$2:$S$2)=MONTH(A$2))*(LEFT(A$3,1)="R")),1)	text	NO

 

[Capper2824]

Thank you again peter for your help.

Could you possibly guide me on how and where to enter this formula, bearing in mind, I am quite a novice at this. sorry for being a pain.

Kind Regards.

Rob.

 

[Peter_SSs]

Could you possibly guide me on how and where to enter this formula, bearing in mind, I am quite a novice at this. sorry for being a pain.

You are not being a pain - this is what the forum is here for.
I will give the steps for the particular layout of my sample in post #17. You will have to adapt to your actual ranges if they are different.

1. Select from the top left cell that could possibly be coloured (A5 for me) down to the bottom right cell (S70 for me) so that the whole range is selected but that top left cell is the 'active' cell.
   
   
2. Home ribbon tab -> Conditional Formatting -> New rule ... -> Use a formula to determine which cells to format -> Format values where this formula is true:-
   =A5=AGGREGATE(14,6,$A$5:$S$70/((MONTH($A$2:$S$2)=MONTH(A$2))*(LEFT(A$3,1)="R")),1) -> Format... -> Fill tab -> Choose the colour you want-> OK -> OK

The important things with that formula are:

• Match the red and purple cell references to the top-left and bottom-right of the actual range you selected in step 1 above.
  
  
• The blue range should cover the same columns as you have selected and match the row that the dates are in (row 2 for me).
  
  
• The orange reference should be to the first column that you have selected but the row with the R/MT/OT values (row 3 for me).
  
  
• With all of the cell/range references in the formula, ensure that you have used $ signs where I have - and nowhere else.

 

[Capper2824]

Again, thank you, I'll try this when I get in from work.

Much appreciated.

Rob.