Forum Discussion

rjtomkinson's avatar
rjtomkinson
Copper Contributor
Nov 14, 2023

Excel formula for finding, ranking and returning nth value

Can anyone explain what formula or array formula I'd need for the following:   Scenario - school cross country results  Each school may have six runners but only the top four score.  Column A lis...
excel
Formulas and Functions
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Nov 21, 2023

rjtomkinson Provided that you are using Excel for 365 the basic formula for your example would be:

 

=TAKE(SORT(FILTER(A2:A45,B2:B45=E1)),4)

 

Though note the slight modifications to cells D1 and E1 where the latter now only contains the the school number, thus not "School 13".

 

Edit:

Added your file with two alternative formulas that should work in older Excel versions.

 

rjtomkinson's avatar
rjtomkinson
Copper Contributor
Nov 21, 2023

Riny_van_Eekelen 

Thank you for this.

How can I extrapolate this formula? I get the alert that I cannot edit an array formula.

I need to be able to copy it across for multiple schools

e.g. School: 13, 18, 41 etc 

each time searching the same table for the 1st to 4th runners for that respective school number.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Nov 21, 2023

    rjtomkinson So you are using the formula surrounded by the curly brackets in I2? That's an old style array formula that needs to be confirmed with Ctrl-Shift-Enter (CSE), i.e. not just Enter. As soon as you touch it you get that error message. You need to select all four cells, enter the formula  and then CSE. Then you can select those four cells again and drag them across. It's actually quite annoying to work with such CSE type formulas. By the way, you need to make the references to columns A and B absolute for copy by dragging to work correctly. Done that in the attached file.

     

    I've also include a traditional solution that avoids CSE. I would use that one if you really can't get your hands on a modern Excel version.

     

     

    • rjtomkinson's avatar
      rjtomkinson
      Copper Contributor
      Nov 21, 2023

      Riny_van_Eekelen 

       

      Thank you for your efforts.

       

      Without understanding the language of the functions and formulas I think I am destined to fail - when using your formula I either get a #ref or #name error.

      Additionally, I want to transfer this to Google Sheets which my school uses, but it doesn't recognise the aggregate function.

      Unfortunately I am no closer to a solution.

       

      Thanks again

      • peiyezhu's avatar
        peiyezhu
        Bronze Contributor
        Nov 21, 2023

         

         Have you tried Excel App on Android smart phone?

        @Riny_van_Eekelen's file works fine on my side.

        Re:

        when using your formula I either get a #ref or #name error

         

        Can you share the workbook with error formular?

        enter the error formular like '=myErroFumular(range)

         

         

        Additionly,you need list 4 runners in each sheets rather than put them(all 4 runners) in one sheet?

         

        rjtomkinson 

Resources