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.

     

OliverScheurich's avatar
OliverScheurich
Gold Contributor
May 16, 2023

mdmiles1485 

=INDEX($A3:$U3,LARGE(IF(($A3:$U3<>"")+($A3:$U3<>0),COLUMN($A:$U)),V$2))

You can try this formula which is in cell V3 and filled across range V3:AE7. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

=LARGE($V3:$AE3,AF$2)

This formula is in cell AF3 and filled across range AF3:AM7.

 

The lowest scores among the last 10 rounds can be returned accordinly.

mdmiles1485's avatar
mdmiles1485
Copper Contributor
May 16, 2023
tried to copy and paste the first formula. I see you refer to V2 for the value of 1 that speaks to the LARGE portion of the formula. I tried to set it up the same as yours. I edited the row I'm reffering to to reflect my own spread sheet.
=INDEX($A4:$CP4,LARGE(IF(($A4:$CP4<>"")+($A4:$CP4<>0),COLUMN($A:$CP)),DA$3))

This resulted in the output #Value!. In my case I have 19 empty scores in the row from right to left before it reaches any values. but I assume with this formula it should look past those 0s until it finds actual scores.
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    May 16, 2023

    mdmiles1485 

    The formula works in my Excel 2013 file if i enter it with ctrl+shift+enter. Arrayformulas have to be entered with ctrl+shift+enter if one doesn't work with Office 365 or Excel 2021.

    • mdmiles1485's avatar
      mdmiles1485
      Copper Contributor
      May 16, 2023
      Okay before I attempt. Do you feel I should try and achieve blanks for cells that currently hold a value of 0? Also thanks so much for even attempting to help me on this. I've been stumped.
      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        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.