Using large in an array function?

Hi all,

I've spent days trying to solve the following problem and have resigned myself to asking for assistance.

Here is a simplified example of what I'm trying to do.

col A col B col C
1 | A | 52
1 | B | 63
1 | C | 84
2 | D | 45..........

This a horse racing spreadsheet were col A is the race number, B is the horse name and C is the prizemoney won. There can be up to 10 races with a maximum of 24 starters.

What I am trying to do is find the top 5 prizemoney earners from each race, then display the horses name followed by the prizemoney below the current sheet.

I have been messing around with an array formula but feel i may be barking up the wrong tree. I know the large function needs to be used somewhere.

Any suggestions would be very much appreciated.

Cheers,
Simon

Hi simonbrownlow,

Put this formula on cell E1:

Please Login or Register  to view this content.

Drag the formula down up to Cell E5.

Then put this formula on cell F1:

Please Login or Register  to view this content.

Drag the formula down up to cell F5

On Cell D1, you can put 1,2,3,4,5 (the highest prizemoney) up to cell D5.

Hope it helps you get started.

Originally Posted by simonbrownlow

Hi all,

I've spent days trying to solve the following problem and have resigned myself to asking for assistance.

Here is a simplified example of what I'm trying to do.

col A col B col C
1 | A | 52
1 | B | 63
1 | C | 84
2 | D | 45..........

This a horse racing spreadsheet were col A is the race number, B is the horse name and C is the prizemoney won. There can be up to 10 races with a maximum of 24 starters.

What I am trying to do is find the top 5 prizemoney earners from each race, then display the horses name followed by the prizemoney below the current sheet.

I have been messing around with an array formula but feel i may be barking up the wrong tree. I know the large function needs to be used somewhere.

Any suggestions would be very much appreciated.

Cheers,
Simon

See attached for a sample.... formulas used are array formulas which must be confirmed with CTRL+SHIFT+ENTER after any alterations and before copying down.

Adjust all ranges to suit your raw data...

Note, formula looks at race number at top of each group to extrapolate corresponding data.

Try this, assuming your data goes from row 1 to 100, adjust as necessary

Specific race number in E1

In F1:F5 single digits 1,2,3,4,5

In G1 this formula to get the horse name

=IF(F1>COUNTIF(A$1:A$100,E$1),"",INDEX(B$1:B$100, MATCH(1,(A$1:A$100=E$1)*(C$1:C$100= LARGE(IF(A$1:A$100=E$1,C$1:C$100),F1)),0)))

confirmed with CTRL+SHIFT+ENTER

In H1 this formula to get the prize money

=IF(G1="","",LOOKUP(2,1/((A$1:A$100=E$1)*(B$1:B$100=G1)),C$1:C$100))

copy both G1 and H1 to row 5

Hi,

Thank you all for your help with my problem.

Just in regards to the spreadsheet provided by NBVC.

I have noticed that if I have tie for the amount of prizemoney won, the index formula pulls out the first name found for both values. For example if I have horses A and B earning 50000 each, the spreadsheet produces the following result.

$/horse

50000/A
50000/A

instead of

$/horse
50000/A
50000/B

Would I use COUNTIF to find the next value?

Thanks,
Simon

Hi simonbrownlow,

You can also use Pivot Table for this case (without using any formulas) .

If you will make changes in the data source you have to refresh the Pivot Table.

See attached sample file.

Originally Posted by simonbrownlow

Hi,

Thank you all for your help with my problem.

Just in regards to the spreadsheet provided by NBVC.

I have noticed that if I have tie for the amount of prizemoney won, the index formula pulls out the first name found for both values. For example if I have horses A and B earning 50000 each, the spreadsheet produces the following result.

$/horse

50000/A
50000/A

instead of

$/horse
50000/A
50000/B

Would I use COUNTIF to find the next value?

Thanks,
Simon

You can change the formula and include the Countif().

In H2:

Please Login or Register  to view this content.

copied down. Same in K2, etc...

See attached for revised sample.