Forum Discussion
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 ...
=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.
I feel I need to combine the Vlookup funcion with the Large Function and then somehow have it all be looking from right to left (since the scores will be entered from left to right)
=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.
- 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.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.