Display best figures from certain columns

[sirdon]

Hi all,

I am trying to display the best figures from certain columns.

I have attached a spreadsheet displaying scores and the desired result that I am looking for.

Basically I would like the order to go in:

1st Number with the highest value

If 1st number is equal the next option is to choose the 2nd Number (after the ‘-‘) with the lowest value.

If 1st and 2nd number is equal the next option is to choose the 3rd number (in brackets) with the lowest value.

As always any help is much appreciated.

Thanks.

[sirdon]

Still need help with this if anyone can offer something.

Many thanks

[:) Sixthsense :)]

All the below formula's are ARRAY FORMULA - Requires CTRL+SHIFT+ENTER

In B2 Cell

Please Login or Register  to view this content.

In C2 Cell

Please Login or Register  to view this content.

In D2 Cell

Please Login or Register  to view this content.

In E2 Cell

Please Login or Register  to view this content.

[sirdon]

WOW!!! Just WOW!!!

That is amazing. I can not thank you enough.

[:) Sixthsense :)]

Glad it helps you and thanks for the feedback

[sirdon]

If you get a chance I have another thread I need help with. This may be more difficult, but as always any help is much appreciated

http://www.excelforum.com/excel-form...11#post3231211

[:) Sixthsense :)]

I have posted a reply to that thread, please check

[sirdon]

Hi i know I thought this was resolved at the time. I have put this project to the side and have now come back to it. While working on it I have realised I need to fix up the bowling figures.

So, what it does is, is turn the bowling figures like “5-15 (10)” into a number 51510, except this fails when the number of significant digits is different for each of the wickets (5), runs (15) and overs (10) from row to row.

Instead we need to calculate a “bowling score” by assigning more value to wickets than runs than overs, eg

Wicket value: 10000
Run value: 10
Over value: 1

We can use the function

=VALUE(LEFT($DB19:$XA19,FIND("-",$DB19:$XA19)-1))*10000 * VALUE(TRIM(MID($DB19:$XA19, FIND("-", $DB19:$XA19)+1, 3)))*10 * VALUE(TRIM(SUBSTITUTE(MID($DB19:$XA19,FIND("(",$DB19:$XA19)+1, 10), ")", "")))

To calculate the “bowling score” for an individual cell in the format “5-15 (10)”, but it’s beyond my skills to substitute that into the full array function.

Any help would be much appreciated.

[sirdon]

I have created a new post here http://www.excelforum.com/excel-form...ml#post4242269