How to obtain highest value in a specific category using a formula?

[abupu]

Hi guys,

Here's the problem im having:

<table border="0" cellpadding="0" cellspacing="0" width="176"><colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:4096;width:84pt" width="112"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl1132" style="height:12.75pt;width:48pt" height="17" width="64">Category</td> <td class="xl1132" style="border-left:none;width:84pt" width="112">Values</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1132" style="height:12.75pt;border-top:none" height="17">1</td> <td class="xl1133" style="border-top:none;border-left:none">10</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1132" style="height:12.75pt;border-top:none" height="17">1</td> <td class="xl1133" style="border-top:none;border-left:none">20</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1132" style="height:12.75pt;border-top:none" height="17">2</td> <td class="xl1133" style="border-top:none;border-left:none">30</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1132" style="height:12.75pt;border-top:none" height="17">2</td> <td class="xl1133" style="border-top:none;border-left:none">40</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1132" style="height:12.75pt;border-top:none" height="17">1</td> <td class="xl1133" style="border-top:none;border-left:none">60</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl1132" style="height:12.75pt;border-top:none" height="17">1</td> <td class="xl1133" style="border-top:none;border-left:none">50</td> </tr> </tbody></table>
I want to find the highest value in category 1, using a formula. The value in this instance should be 60.

Any idea how i could write this as a formula?

PS: the category rows will always be in random order so would like to do this without needing to sort the category.

 

[abupu]

Thanks guys! Got it all working