Forum Discussion

mdmiles1485's avatar
mdmiles1485
Copper Contributor
May 16, 2023
Solved

Please Help

I'm continuing work on my golf league spread sheet.   I need to create a formula that Finds the Largest 9 scores in a row.  And another formula that finds the Lowest 8 scores in a row.   Here is the ...
excel
  • OliverScheurich's avatar
    OliverScheurich
    May 16, 2023

    mdmiles1485 

    =INDEX($A4:$CP4,LARGE(IF(($A4:$CP4<>"")*($A4:$CP4<>0),COLUMN($A:$CP)),DA$3))

    Thanks for your reply. I've changed the formula and now it ignores blank cells and cells with 0. Now you don't need to change cells with 0 values.

     

sanjibdutta's avatar
sanjibdutta
Brass Contributor
May 16, 2023

Himdmiles1485 

I see Quadruple_Pawn  has already provided the correct solution while I was working on it ( was a little distracted by other things).

However, here is my solution for old and new version of excel in case you are interested in otherways to do this.

Old Excel version:

10 Largest: =IFERROR(LARGE(IF((A4:CP4<>"")*(A4:CP4<>0),A4:CP4,""),COLUMN(A1:J1)),"")

10 Smallest:=IFERROR(SMALL(IF((A4:CP4<>"")*(A4:CP4<>0),A4:CP4,""),COLUMN(A1:J1)),"")

Modern Excel vesion:

10 Largest: =IFERROR(LARGE(FILTER(A4:CP4,(A4:CP4>0)*(A4:CP4<>"")),COLUMN(A1:J1)),"")

10 Smallest:=IFERROR(SMALL(FILTER(A4:CP4,(A4:CP4>0)*(A4:CP4<>"")),COLUMN(A1:J1)),"")

You need to customize it according to the position of your data. For  number of result you need to return Just change "J" to row letter of number. You can also use like "{1,2,3,4,5,6,7,8,9,10}" instead of "COLUMN(A1:J1)"

Attaching file with contribution of my part in the same excel with example...Thanks