I have a list of costs displayed against various building elements.
The elements are in a fixed order but the resulting associated cost
will be a random turn out figure. I want to extract the maximum cost
and display this cost with the associated label e.g. Brickwork
£20,000.00. I know how to use maximum for the costs but cant find a
method to link in the associated label. Can anybody help?


--
CMarkG
------------------------------------------------------------------------
CMarkG's Profile: http://www.excelforum.com/member.php...o&userid=27694
View this thread: http://www.excelforum.com/showthread...hreadid=472080

Here's a formula system that will take into consideration any ties for
first place...

Assumptions:

A2:A6 contains the 'building element'

B2:B6 contains the 'cost'

Formulas:

C2, copied down:

=RANK(B2,$B$2:$B$6)+COUNTIF($B$2:B2,B2)-1

D1: enter 1, indicating you want the top cost

E1:

=MAX(IF(B2:B6=INDEX(B2:B6,MATCH(D1,C2:C6,0)),C2:C6 ))-D1

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER

F2, copied down and over to the next column:

=IF(ROWS(F$2:F2)<=$D$1+$E$1,INDEX(A$2:A$6,MATCH(RO WS(F$2:F2),$C$2:$C$6,0)
),"")

Note that if you want a Top 3 list, change the 1 in D1 to 3, and so on...

Hope this helps!

In article ,
CMarkG wrote:

I have a list of costs displayed against various building elements.
The elements are in a fixed order but the resulting associated cost
will be a random turn out figure. I want to extract the maximum cost
and display this cost with the associated label e.g. Brickwork
£20,000.00. I know how to use maximum for the costs but cant find a
method to link in the associated label. Can anybody help?

Many thanks Domenic, your solution worked a treat. This is the first
time I've used a forum - you've set a high standard for prompt helpful
answers.


--
CMarkG
------------------------------------------------------------------------
CMarkG's Profile: http://www.excelforum.com/member.php...o&userid=27694
View this thread: http://www.excelforum.com/showthread...hreadid=472080