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