Finding highest value when column contains two entries with the same figure

[halesowenmum]

Hi, here is an example of the data I am working with which is giving me a problem as it peskily contains two values which are the same (rrrr):

162</SPAN>

227

196</SPAN>

191</SPAN>

206</SPAN>

227

212</SPAN>

198</SPAN>

222</SPAN>

193</SPAN>

194</SPAN>

127

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

I only need to see the TOP value - how do I deal with this??? Do I simply take those two figures and average them - would that work??

At the moment I have it set up using conditional formatting to highlight the top value in red and the bottom value in light blue so its just these ones where there's two top values that are giving me a pain in the neck.

Why do these things always happen when you're on a really urgent piece of work?!

 

[RoryA]

=MAX(B40:B51)
for the highest and
=MIN(B40:B51)
for the lowest.

 

[halesowenmum]

Roger. Thanks.

 

[Snakehips]

Excel Workbook
	A	B
40	Jan total	162
41	Feb total	227
42	Mar total	196
43	Apr total	191
44	May total	206
45	Jun total	227
46	Jul total	212
47	Aug total	198
48	Sep total	222
49	Oct total	193
50	Nov total	194
51	Dec total	127
52
53	Highest	227
54	Lowest	127
Sheet7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B51	1. / Formula is =ROW(A1)=MATCH($B$54,$B$40:$B$51,0)	Abc
B51	2. / Formula is =ROW(A1)=MATCH($B$53,$B$40:$B$51,0)	Abc