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]

Some cells have overlapping conditional formatting ranges. Earlier versions of Excel will not evaluate all of the conditional formatting rules on the overlapping cells. The overlapping cells will show different conditional formatting.			27
			SUPER 6 DATA'!W5:AE70	Excel 97-2003
			SUPER 6 DATA'!M5:U70
			SUPER 6 DATA'!AI5:AQ70
			SUPER 6 DATA'!AS5:BA70
			SUPER 6 DATA'!BC5:BK70
			SUPER 6 DATA'!BO5:BX70
			SUPER 6 DATA'!BZ5:CH70
			SUPER 6 DATA'!CJ5:CR70
			SUPER 6 DATA'!C5:K70
One or more functions in this workbook are not available in earlier versions of Excel. When recalculated in earlier versions, these functions will return a #NAME? error instead of their current results.			1
			SUPER 6 DATA'!C5:CR70	Excel 97-2003
Hello again, I have put that formula in as you have wrote it but it is returning the above result, I did originally create this in an earlier version of excel. would this be a problem and could I correct this? the formula you gave me is returning the the highest score but not clearing the previous month? Again I do apologise for this. Feel free if you want to give up on this one. Regards Rob.				Excel 2007

 

[Peter_SSs]

the formula you gave me is returning the the highest score but not clearing the previous month?

This seems to be saying that the results shown in post 17 is not what you want. If that is the case, why didn't you say so rather than asking me how to implement a suggestion that is not what you want?
If I have not understood your comments correctly, please try to clarify.

How would we determine what is the latest month? By today's date?