maximum value and associated labels

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CMarkG - 30 Sep 2005 14:10 GMT

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?

Signature

CMarkG

Reply to this Message

Domenic - 30 Sep 2005 19:16 GMT

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(ROWS(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!

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

Reply to this Message

CMarkG - 30 Sep 2005 21:47 GMT

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.

Signature

CMarkG

Reply to this Message