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?!

 

[Snakehips]

o'roit ower kid,

Try like .........

Excel Workbook
	A
1	162
2	227
3	196
4	191
5	206
6	227
7	212
8	198
9	222
10	193
11	194
12	127
13
14
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =ROW(A1)=MATCH(MAX($A$1:$A$20),$A$1:$A$20,0)	Abc

Hope that helps.

 

[RoryA]

They are both the top value so why don't you want them both highlighted? On what basis is one preferable to the other?

 

[halesowenmum]

They are both the top value so why don't you want them both highlighted? On what basis is one preferable to the other?

I don't want them both highlighted and that's exactly the point you see... but since they are both the highest values if you use CF to highlight the data range to show Top 1 and Bottom 1 then you get two red highlights (for Top values) whererever you have two top values that are exactly the same. I am about to try Snakehips' colloquially phrased suggestion and see if it helps at all!!

So I understand enough to know that I don't know enough to know mathematically what I need to show here when I get this two results for the same group of values..... (I am absolutely pants at maths and i don't even understand statistically how I should handle this type of situation - just plain thick I guess ).

Ahh.

 

[halesowenmum]

Sorry, I'm totally not getting this (it's me, not you lol!).

Am I just selecting the cell below the column of result values that I need to analyse, or am I selecting the whole range when I apply this? If the former presumably I'd need to paste Format down to replicated it??

Am assuming your instruction applies to using CF rule type 'Use a formula...' - but it isn't seeming to work.

Just for clarity and in the face of my mathematical thickness, can you use the following table (which repesents the actual physical location within the worksheet where this data now sits) to help me nail this one?:

	A</SPAN>	B</SPAN>
39</SPAN>		00:00</SPAN>
40</SPAN>	Jan total </SPAN>	162</SPAN>
41</SPAN>	Feb total </SPAN>	227</SPAN>
42</SPAN>	Mar total </SPAN>	196</SPAN>
43</SPAN>	Apr total </SPAN>	191</SPAN>
44</SPAN>	May total </SPAN>	206</SPAN>
45</SPAN>	Jun total </SPAN>	227</SPAN>
46</SPAN>	Jul total </SPAN>	212</SPAN>
47</SPAN>	Aug total </SPAN>	198</SPAN>
48</SPAN>	Sep total </SPAN>	222</SPAN>
49</SPAN>	Oct total </SPAN>	193</SPAN>
50</SPAN>	Nov total </SPAN>	194</SPAN>
51</SPAN>	Dec total </SPAN>	127</SPAN>
52</SPAN>		Cell B52</SPAN>

<TBODY>
</TBODY>

Thank you!!

 

[RoryA]

My question was why don't you want them both highlighted? Why should one be highlighted rather than the other?

 

[halesowenmum]

I have a row below, one to show the Highest Value, the next row down to show the Lowest Value. By definition then that's one value to be represented in the Highest Value category and another in the Lowest. However, these aren't just numbers, they're how many patients came to a particular health centre. We need to see where the peaks are and we've already got tables which show the top to and next top two activity peaks by time of day/month, but for this we are needing to take the top 1 and the bottom 1 to make some calculations around how many consulting rooms would we need to cope with these peaks of activity.

So do I simply just say that 227 (for example) is the peak and it doesn't matter if it appears twice because even if it appears twice, that's still the peak?? Is that as simple as it is?

That's what I'm asking. Blame my 1970s teachers who left me behind at long division and long multiplication - I find maths baffling for the most part and I don't understand a lot of it. What may seem obvious to you, isn't necessarily obvious to me.

 

[RoryA]

Yes, 227 is the highest value. I think it's quite useful to see that it occurred more than once.

 

[halesowenmum]

Incidentally, If I use CF to show the values in Col B that are the Top 1 and the Bottom 1, making the top one bright green and the bottom value pale green:

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

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

In cell B53 I needed to show which value was the highest by (preferably) that formula either looking for the highest value (and returning the value) or looking for which cell was highlighted bright green (and return that value), then the same for the bottom values in B54.

Is there a formula to do that???

 

[Snakehips]

Ref post #5....

Conditional formatting >> Applies to range $B$40:$B$51
Apply Formula >> as below.

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
Sheet7

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