Replacing Lowest value

[KrisGaczewski]

Hi Team,
We are a Seniors Golf Club with members ranging in age from 60 to 80, so we are not very savvy with Excel but are hoping to automate a system we are currently doing by hand.
The issue is calculating the Handicap of players (I hope you are familiar with golf terminology).
We are not using the Word Handicap System but are currently calculating handicap by hand using the Stableford System.

IS IT POSSIBLE:
Enter Score in G3 (green) after pressing enter - replace the lowest value in in the range of L3 to AE3 (Yellow)
After next game I want this to repeat Enter New score in G3 - and the lowest number in the range L3 to AE3 will be replace
Then copy formula for next player Enter G4 and replace in L4 to AE4 and so on
I have attached an example in an Excel file without this function

 

[dermie_72]

One way to do this would be to have a "NEW SCORE" Section to the right....Say starting in AG:AZ then using a formula in that field to cross reference the information.
Highlight AG3:AZ3 ensuring AG3 is the active cell, then put this formula in and press Control + Enter

=IF(L3=MIN($L3:$AE3),$G3,L3)

This will replace the lowest score with the new score value in G3

 

[KrisGaczewski]

One way to do this would be to have a "NEW SCORE" Section to the right....Say starting in AG:AZ then using a formula in that field to cross reference the information.
Highlight AG3:AZ3 ensuring AG3 is the active cell, then put this formula in and press Control + Enter

=IF(L3=MIN($L3:$AE3),$G3,L3)

This will replace the lowest score with the new score value in G3

One way to do this would be to have a "NEW SCORE" Section to the right....Say starting in AG:AZ then using a formula in that field to cross reference the information.
Highlight AG3:AZ3 ensuring AG3 is the active cell, then put this formula in and press Control + Enter

=IF(L3=MIN($L3:$AE3),$G3,L3)

This will replace the lowest score with the new score value in G3

Hi Dermie_72,
Thank you very much
You are Star
I will try this Tonight
I am using Excel 2021
Regards
Kris

 

[KrisGaczewski]

Hi Dermie_72,
Thank you very much
You are Star
I will try this Tonight
I am using Excel 2021
Regards
Kris

Hi Dermie_72,
Thank you very much
You are Star
I will try this Tonight
I am using Excel 2021
Regards
Kris

One way to do this would be to have a "NEW SCORE" Section to the right....Say starting in AG:AZ then using a formula in that field to cross reference the information.
Highlight AG3:AZ3 ensuring AG3 is the active cell, then put this formula in and press Control + Enter

=IF(L3=MIN($L3:$AE3),$G3,L3)

This will replace the lowest score with the new score value in G3

Hi Dermie_72,
I didn't explain what I am looking for
Please Download my Excel so you can see what I am trying to do
Link: OVGC.xlsx
Thank you
Regards
Kris

 

[Peter_SSs]

Welcome to the MrExcel board!

I'm not sure that suggestion is what you need. In your sample, if L3 and M3 were both 8 and the new score is 20, wouldn't you only want one of the two "8" values changed to 20, not both?

If I am right then you could try this Worksheet_Change event code. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by entering new values in column G for various players. The code runs automatically when a value is entered in col G.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Notes

1. After the new value is entered and the lowest value replaced, do you want the player's scores in columns L:AE sorted lowest to highest as is the case with your one example? If so this code does that (but there would be a better way if you always want sorted data). If you do not want it sorted then remove the ".Sort ..." code line
   
   
2. I'm wondering if you could have the circumstance where a newer player does not yet have 20 scores. If you do then presumably a new score in col G would just get added to the player's score list rather than replacing the lowest score? The code could be adapted to cope with that circumstance if you want to proceed down this vba path.
   
   
3. Does there need to be check that the value entered in col G is bigger than the lowest score in cols L:AE? I'm imagining that you would not want the lowest score replaced by an even lower score if a low score was accidentally entered in col G? Isn't the list supposed to be the best 20 scores?
   
   
4. Not related to your actual question, but please don't "over-quote". In post #3 you have quoted post #2 twice and in post #4 you have quoted post #3 twice and also quoted post #2 which isn't even needed in your post, it's just filling up space.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range, rMin As Range
 
  Set Changed = Intersect(Target, Columns("G"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If IsNumeric(c.Value) Then
        With Intersect(c.EntireRow, Columns("L:AE"))
          Set rMin = .Find(what:=Application.Min(Range(.Address)), After:=.Cells(.Cells.Count), LookAt:=xlWhole)
          If Not rMin Is Nothing Then
            rMin.Value = c.Value
            .Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo, Orientation:=xlLeftToRight
          End If
        End With
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[KrisGaczewski]

Welcome to the MrExcel board!

I'm not sure that suggestion is what you need. In your sample, if L3 and M3 were both 8 and the new score is 20, wouldn't you only want one of the two "8" values changed to 20, not both?

If I am right then you could try this Worksheet_Change event code. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by entering new values in column G for various players. The code runs automatically when a value is entered in col G.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Notes

1. After the new value is entered and the lowest value replaced, do you want the player's scores in columns L:AE sorted lowest to highest as is the case with your one example? If so this code does that (but there would be a better way if you always want sorted data). If you do not want it sorted then remove the ".Sort ..." code line
   
   
2. I'm wondering if you could have the circumstance where a newer player does not yet have 20 scores. If you do then presumably a new score in col G would just get added to the player's score list rather than replacing the lowest score? The code could be adapted to cope with that circumstance if you want to proceed down this vba path.
   
   
3. Does there need to be check that the value entered in col G is bigger than the lowest score in cols L:AE? I'm imagining that you would not want the lowest score replaced by an even lower score if a low score was accidentally entered in col G? Isn't the list supposed to be the best 20 scores?
   
   
4. Not related to your actual question, but please don't "over-quote". In post #3 you have quoted post #2 twice and in post #4 you have quoted post #3 twice and also quoted post #2 which isn't even needed in your post, it's just filling up space.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range, rMin As Range
 
  Set Changed = Intersect(Target, Columns("G"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If IsNumeric(c.Value) Then
        With Intersect(c.EntireRow, Columns("L:AE"))
          Set rMin = .Find(what:=Application.Min(Range(.Address)), After:=.Cells(.Cells.Count), LookAt:=xlWhole)
          If Not rMin Is Nothing Then
            rMin.Value = c.Value
            .Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo, Orientation:=xlLeftToRight
          End If
        End With
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

Hi Peter,
I am new to this Board - make mistakes
If you can Please download my excel
Link: OVGC.xlsx
I will be more clear what I am trying to do
There will be situation with new player , to make simple we can enter scores manually
we take average 8 lowest out of 20 scores
We are using simple StableFord system
18 points is handicap 0
16 points is handicap 2 ( 18-16=2)
6 points is handicap 12 ( 18-6 =12
22 points is handicap 0 (18-22= -4)
Thank you
Regards
Kris

 

[Peter_SSs]

I am new to this Board - make mistakes

Yes, understand that so try to point out things that help.

If you can Please download my excel

I did download your workbook. Did you try my code? To me, it looks like my code would mean that you wouldn't even need columns AG:AZ because your formula in AF could point directly at the values in L:AE which would already be sorted.

we take average 8 lowest out of 20 scores

Hmm, if they are stableford scores, wouldn't you be looking at the highest scores. Where I play if I get a high stableford score my handicap is likely to be reduced.
In your system it looks like if I deliberately play badly and score zero stableford points my handicap would immediately increase.

 

[KrisGaczewski]

Yes, understand that so try to point out things that help.


I did download your workbook. Did you try my code? To me, it looks like my code would mean that you wouldn't even need columns AG:AZ because your formula in AF could point directly at the values in L:AE which would already be sorted.



Hmm, if they are stableford scores, wouldn't you be looking at the highest scores. Where I play if I get a high stableford score my handicap is likely to be reduced.
In your system it looks like if I deliberately play badly and score zero stableford points my handicap would immediately increase.

HI Peter,
You right about stableford points - it is opposite
I delete , I was trying to do different formulas to check if I have the same outcome
I don't trust my excel knowledge
I will try your code
Thank you
Kris

 

[KrisGaczewski]

HI Peter,
You right about stableford points - it is opposite
I delete , I was trying to do different formulas to check if I have the same outcome
I don't trust my excel knowledge
I will try your code
Thank you
Kris

Hi Peter,

You code is perfect, you are Genius

Now I realise what I am doing is wrong.

There is NO SORTING, NO REPLACING the lowest number.

((((Handicaps are calculated by averaging the eight lowest scores from the last 20 rounds played, and then multiplying the result by 0.93. Every new round played deletes the oldest one in the 20-round span, which can explain why a handicap can change dramatically if a particularly good score drops out.)))

• It looks like should be done this way:
• Every time when you Enter score in G3
• Value will be always added to L3 and the previous value will be span (move, push)
• to next cell M3,N3,O3….
• After we reach 20 scores , average will be done selecting 8 lowest scores out of 20 scores in
• What we are doing now for new player (manually)
• Player must submit 3 score cards – we average by 3 to get his handicap (then 4,6,8)
• 0.93 some time we change to 0.94 or 0.95 for winter

Thank you for your patience

Link to download Excel: V1.xlsm

Regards

Kris

 

[Peter_SSs]

You code is perfect,

Except it doesn't do what you have set out in your bullet points.

That can be fixed but first can you clarify the following point?

Player must submit 3 score cards – we average by 3 to get his handicap (then 4,6,8)

When they submit their 3 cards you average those 3.
At what point (ie after how many cards) do you change to averaging the lowest 4?
At what point (ie after how many cards) do you change to averaging the lowest 6?
At what point (ie after how many cards) do you change to averaging the lowest 8?