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

 

[JGordon11]

Book4
	A	B	C
1	Date	Name	Score
2	1-May	a	17
3	8-May	b	10
4	15-May	c	18
5	22-May	d	15
6	29-May	e	12
7	5-Jun	f	17
8	12-Jun	g	10
9	19-Jun	h	17
10	26-Jun	i	14
11	3-Jul	j	20
12	10-Jul	k	16
13	17-Jul	l	10
14	24-Jul	m	16
15	31-Jul	n	17
16	7-Aug	o	20
17	14-Aug	p	11
18	21-Aug	q	19
19	28-Aug	r	13
20	4-Sep	s	15
21	11-Sep	t	10
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:C21	Expression	=$C2=MAX($C$2:$C$21*(MONTH($A2)=MONTH($A$2:$A$21)))	text	NO

 

[Capper2824]

Book4
	A	B	C
1	Date	Name	Score
2	1-May	a	17
3	8-May	b	10
4	15-May	c	18
5	22-May	d	15
6	29-May	e	12
7	5-Jun	f	17
8	12-Jun	g	10
9	19-Jun	h	17
10	26-Jun	i	14
11	3-Jul	j	20
12	10-Jul	k	16
13	17-Jul	l	10
14	24-Jul	m	16
15	31-Jul	n	17
16	7-Aug	o	20
17	14-Aug	p	11
18	21-Aug	q	19
19	28-Aug	r	13
20	4-Sep	s	15
21	11-Sep	t	10
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:C21	Expression	=$C2=MAX($C$2:$C$21*(MONTH($A2)=MONTH($A$2:$A$21)))	text	NO

Book4
	A	B	C
1	Date	Name	Score
2	1-May	a	17
3	8-May	b	10
4	15-May	c	18
5	22-May	d	15
6	29-May	e	12
7	5-Jun	f	17
8	12-Jun	g	10
9	19-Jun	h	17
10	26-Jun	i	14
11	3-Jul	j	20
12	10-Jul	k	16
13	17-Jul	l	10
14	24-Jul	m	16
15	31-Jul	n	17
16	7-Aug	o	20
17	14-Aug	p	11
18	21-Aug	q	19
19	28-Aug	r	13
20	4-Sep	s	15
21	11-Sep	t	10
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:C21	Expression	=$C2=MAX($C$2:$C$21*(MONTH($A2)=MONTH($A$2:$A$21)))	text	NO

Thank you for your reply, I can get the format to do this but I would like the next highest score which would be above 17 to supersede the previous score and 18 or above to cancel the Cyan format out and carry on like this throughout the ten months, if you can understand my meaning, I am quite a novice at this, sorry.

Regards Rob.

 

[JGordon11]

Sorry - I don't quite follow. If you can post what your data looks like and how you would like it to look after the conditional formatting is applied, I may be able to help.

xl2bb add-in is a good way to post your worksheet examples, see XL2BB - Excel Range to BBCode

 

[Capper2824]

Hi sorry for the late reply I have tried downloady the file but cannot or I don't understand how to work the xl2bb?

In the example above the first month which consisted of anywhere between 4 to 7 weeks, in this first month 2 members of the above football league both scored 17 points (at this stage everything is working as I require.

In month 2, 1 person in that month scored 16, yes this person is the top scorer at the moment for the second month, but this person has not equalled or beaten the first months top scorers of 17.

I only want the formula to highlight in Cyan, the top single highest scorer/s in theany month, so in other words if 17 stays the top single score and not equalled or beaten, then they are the only ones I need highlighting in colour Cyan.

Bearing in mind that I do not want the Monthly Total (MT)or Overall Total (OT) touched so I need to omit these or not include these in this particular formula.

Hope this make a little more sense ?

 

[Peter_SSs]

I have tried downloady the file but cannot or I don't understand how to work the xl2bb?

Did you follow the link in the earlier link to here?

At what point of this process do you get stuck and what are the symptoms?

@JGordon11
In future it might be better to provide a link to the more detailed XL2BB instruction page.

 

[Capper2824]

Hi sorry for the late reply I have tried downloady the file but cannot or I don't understand how to work the xl2bb?

In the example above the first month which consisted of anywhere between 4 to 7 weeks, in this first month 2 members of the above football league both scored 17 points (at this stage everything is working as I require.

In month 2, 1 person in that month scored 16, yes this person is the top scorer at the moment for the second month, but this person has not equalled or beaten the first months top scorers of 17.

I only want the formula to highlight in Cyan, the top single highest scorer/s in theany month, so in other words if 17 stays the top single score and not equalled or beaten, then they are the only ones I need highlighting in colour Cyan.

Bearing in mind that I do not want the Monthly Total (MT)or Overall Total (OT) touched so I need to omit these or not include these in this particular formula.

Hope this make a little more sense ?

Did you follow the link in the earlier link to here?

At what point of this process do you get stuck and what are the symptoms?

@JGordon11
In future it might be better to provide a link to the more detailed XL2BB instruction page.

I have tried to follow link but I don't understand ho to incorporate it into Microsoft 365.

I have also tried to send a screenshot of what I am asking but says it is too big? It is just under 2mb.

If anyone has WhatsApp I could send screenshot there?

 

[Peter_SSs]

I have tried to follow link but I don't understand ho to incorporate it into Microsoft 365.

That doesn't tell us what point in the process you got up to. If you follow the steps correctly, you don't have to incorporate it into Excel at all, it appears in the ribbon as shown in the article

I have also tried to send a screenshot of what I am asking but says it is too big? It is just under 2mb.

If it is that big we definitely do not need that screenshot. We need something small, but representative. But we also cannot copy from an image screenshot so we cannot test with a lot of typing - which most helpers, naturally, are not that keen on.

If anyone has WhatsApp I could send screenshot there?

Please don't do that - all information should remain public and available to all forum users. It would effectively be a breach of #4 of the Forum Rules

 

[Capper2824]

Ok thank you I will try it tonight when I get back from work. Thank you for your help.

 

[Peter_SSs]

No problem. You just need to work carefully through the instructions given and also keep in mind the things mentioned right near the end of the main article, particularly the one I have marked here.

We have had very few people unable to get XL2BB going in the end.