Forum Discussion
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 catch though. I need my formula to Only ever look at the last 10 scores in the row. The goal is to achieve a handicap for each player. But I don't want to look at all of their scores for the year. Just the last 10 to reflect an accurate Handicap. The other issue with this is that I need The Formula to skip blank entries or entries with a value of zero. ( because not every player will play every week and therefore will have no score for that week.) Is there any possible way to do this. Thank in advance
=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.
10 Replies
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
- 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.
