Replacing Lowest value

KrisGaczewski · Mar 25, 2024

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

KrisGaczewski · Mar 26, 2024

Peter_SSs said:
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?

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?

Hi Peter,
Thanks very much for respond
I have 20 scores for each player in spreadsheet - I will copy and paste
NEW PLAYER: ( we don't have many , may be 2 per year )
To make live easy will do new player manually ( or on separate sheet)
Separate spread sheet will better for us .
Once I have 20 scores for new player I will add to the spreadsheet
What they doing now for new player is:
Players are 60 to 80 years old , they only play 9 holes Corse rating is 29
New Player Scores: 40 , 42 , 43 , 39 , 42 , 40 , 42
- 3 Cards Score 40 , 42 , 43 ( 40 is the lowest then 40-29 = 11 Handicap *0.93
-4 Card Score 44 no changes 40 was the lowest score
-5 Card Score 39 this is now the lowest score ( 39-29= 10 Handicap *0.93
-6 Card Score 42 No changes
-7 Card Score 40 No changes
-8 Card Score 42 Formula takes over
Thank you
Regards
Kris

Peter_SSs · Mar 27, 2024

If I have understood, then you should not need a separate sheet foe newcomers. Try a test with the following arrangements.

I have put the "seasonal multiplier" in cell C1. Could be anywhere but better to only have to change it in one place rather than in every formula that uses it.
I have put a new formula in col AF. If there are no entries in the row the cell appears blank. If there is less than 8 entries then the formula takes the minimum score and subtracts 29. (I think that is what you last post was saying). if there are 8 or more entries then the formula averages the smallest 8.
I have changed the formula in col E to use the multiplier value in C1
The new Worksheet_Change code is below.

KrisGaczewski_1.xlsm

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

1

Seasonal multiplier

0.93

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

Sort 8 out 20 TO DO

2

Handicap

New Score

3

ARCHIBALD

Kevin

13

3

12

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

3.875

4

BARGLOWSKI

Mario

3

20

18

16

14

12

15

16

13

17

18

15.125

5

BERZINS

Ray

1

18

17

6

BOWES

Colin

0

18

7

BRUCE

Gordon

7

40

42

43

11

8

BUCKLEY

David

8

47

42

44

43

38

9

BURN

Stewart

MAIN

Cell Formulas
Range	Formula
E3:E8	E3	=IF(AF3="","",MAX(0,18-AF3)*C$1)
AF3:AF8	AF3	=IF(L3="","",IF(S3="",MIN(L3:R3)-29,AVERAGE(SMALL(L3:AE3,SEQUENCE(8)))))

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c 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 Range("M" & c.Row).Resize(, 19)
          .Value = .Offset(, -1).Value
          .Cells(1, 0).Value = c.Value
        End With
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

KrisGaczewski · Mar 27, 2024

Peter_SSs said:
If I have understood, then you should not need a separate sheet foe newcomers. Try a test with the following arrangements.

I have put the "seasonal multiplier" in cell C1. Could be anywhere but better to only have to change it in one place rather than in every formula that uses it.

I have put a new formula in col AF. If there are no entries in the row the cell appears blank. If there is less than 8 entries then the formula takes the minimum score and subtracts 29. (I think that is what you last post was saying). if there are 8 or more entries then the formula averages the smallest 8.

I have changed the formula in col E to use the multiplier value in C1

The new Worksheet_Change code is below.

KrisGaczewski_1.xlsm
B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF
1 Seasonal multiplier 0.93 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Sort 8 out 20 TO DO
2 Handicap New Score
3 ARCHIBALD Kevin 13 3 3 12 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 3.875
4 BARGLOWSKI Mario 3 20 20 18 18 18 16 14 12 15 16 13 17 18 18 18 18 18 18 18 18 18 15.125
5 BERZINS Ray 1 18 18 17 17 17 17 17 17 17 17 17 17 17 17 17 17 17 17 17 17 17 17
6 BOWES Colin 0 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18
7 BRUCE Gordon 7 40 40 42 43 11
8 BUCKLEY David 8 47 47 42 44 43 43 38 9
9 BURN Stewart
MAIN
Cell Formulas
Range Formula
E3:E8 E3 =IF(AF3="","",MAX(0,18-AF3)*C$1)
AF3:AF8 AF3 =IF(L3="","",IF(S3="",MIN(L3:R3)-29,AVERAGE(SMALL(L3:AE3,SEQUENCE(8)))))

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range) Dim Changed As Range, c 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 Range("M" & c.Row).Resize(, 19) .Value = .Offset(, -1).Value .Cells(1, 0).Value = c.Value End With End If Next c Application.EnableEvents = True End If End Sub

Hi Peter,

Thank you very much
Is working perfectly , excellent
Simple straight forward CODE – amazing
In the morning, I went to Golf Club and show to players and handicapper.
They very impress and happy - we can see the light at end of tunnel.
Feed back from Handicapper:
On my Laptop I am using Excel 2021 – Club computer is running Excel 2019

Excel 2019 Don’t have SEQUENCE and SORT Function ( may be more is missing)
At this time, we don’t have money to upgrade. We are oldies ( pensioners )
Is it possible to use other function in AF3 or may be CODE ( and less we have to upgrade to 2021 )
The love what you done for players that have 20 scores – the only comment was:
Please remove function for New Player –
They want to do it , whatever they doing up to now
The comment was : “More of us are dying then joining”
I don’t know if it is me or excel – I was trying to protect the sheet
So , the only G Column is editable and nothing was working

Much appreciated
Thank you very much
Regards
Kris
P.S.
The club members as me. How we can reward you for all your work
Happy Easter! Enjoy this special day.

Peter_SSs · Mar 28, 2024

KrisGaczewski said:
Excel 2019 Don’t have SEQUENCE and SORT Function ( may be more is missing)

SORT is not an issue because the SORT function is not used in my structure.

KrisGaczewski said:
Is it possible to use other function in AF3 or may be CODE

Yes, see sheet below. The formula will need to be confirmed with Ctrl+Shift+Enter, not just Enter before copying down.

KrisGaczewski said:
The love what you done for players that have 20 scores – the only comment was:

Please remove function for New Player –

They want to do it , whatever they doing up to now

OK, I have removed the "new player" calculations from the col AF formula. That formula is now expecting 20 values (or zero values) in the row.

KrisGaczewski said:
I don’t know if it is me or excel – I was trying to protect the sheet

So , the only G Column is editable and nothing was working

Change the vba code to this. Just the coloured lines have been added.
Change the password to your password or if you are not using a password just remove the two lots of red text

Rich (BB code):

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
 
  Set Changed = Intersect(Target, Columns("G"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    ActiveSheet.Unprotect Password:="abc"
    For Each c In Changed
      If IsNumeric(c.Value) Then
        With Range("M" & c.Row).Resize(, 19)
          .Value = .Offset(, -1).Value
          .Cells(1, 0).Value = c.Value
        End With
      End If
    Next c
    ActiveSheet.Protect Password:="abc"
    Application.EnableEvents = True
  End If
End Sub

KrisGaczewski_2.xlsm

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

1

Seasonal multiplier

0.93

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

Sort 8 out 20 TO DO

2

Handicap

New Score

3

ARCHIBALD

Kevin

13

3

12

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

3.875

4

BARGLOWSKI

Mario

4

10

20

18

16

14

12

15

16

13

17

18

14.125

5

BERZINS

Ray

1

18

17

6

BOWES

Colin

0

14

18

17.5

7

BRUCE

Gordon

8

BUCKLEY

David

MAIN

Cell Formulas
Range	Formula
E3:E8	E3	=IF(AF3="","",MAX(0,18-AF3)*C$1)
AF3:AF8	AF3	=IF(L3="","",AVERAGE(SMALL(L3:AE3,{1,2,3,4,5,6,7,8})))
Press CTRL+SHIFT+ENTER to enter array formulas.

KrisGaczewski · Apr 2, 2024

Peter_SSs said:
SORT is not an issue because the SORT function is not used in my structure.

Yes, see sheet below. The formula will need to be confirmed with Ctrl+Shift+Enter, not just Enter before copying down.

OK, I have removed the "new player" calculations from the col AF formula. That formula is now expecting 20 values (or zero values) in the row.

Change the vba code to this. Just the coloured lines have been added.
Change the password to your password or if you are not using a password just remove the two lots of red text

Rich (BB code):

Private Sub Worksheet_Change(ByVal Target As Range) Dim Changed As Range, c As Range Set Changed = Intersect(Target, Columns("G")) If Not Changed Is Nothing Then Application.EnableEvents = False ActiveSheet.Unprotect Password:="abc" For Each c In Changed If IsNumeric(c.Value) Then With Range("M" & c.Row).Resize(, 19) .Value = .Offset(, -1).Value .Cells(1, 0).Value = c.Value End With End If Next c ActiveSheet.Protect Password:="abc" Application.EnableEvents = True End If End Sub

KrisGaczewski_2.xlsm
B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF
1 Seasonal multiplier 0.93 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Sort 8 out 20 TO DO
2 Handicap New Score
3 ARCHIBALD Kevin 13 3 3 12 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 3.875
4 BARGLOWSKI Mario 4 10 10 20 18 18 18 16 14 12 15 16 13 17 18 18 18 18 18 18 18 18 14.125
5 BERZINS Ray 1 18 18 17 17 17 17 17 17 17 17 17 17 17 17 17 17 17 17 17 17 17 17
6 BOWES Colin 0 14 14 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 17.5
7 BRUCE Gordon
8 BUCKLEY David
MAIN
Cell Formulas
Range Formula
E3:E8 E3 =IF(AF3="","",MAX(0,18-AF3)*C$1)
AF3:AF8 AF3 =IF(L3="","",AVERAGE(SMALL(L3:AE3,{1,2,3,4,5,6,7,8})))
Press CTRL+SHIFT+ENTER to enter array formulas.

Hi Peter,
Thank you very much
Sorry I was away for few days
I will try and let you know
Thank you
Kris

Replacing Lowest value

KrisGaczewski

New Member

Attachments

KrisGaczewski

New Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

KrisGaczewski

New Member

Peter_SSs

MrExcel MVP, Moderator

KrisGaczewski

New Member

Similar threads

Forum statistics

Share this page

Replacing Lowest value

KrisGaczewski

New Member

Attachments

KrisGaczewski

New Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

KrisGaczewski

New Member

Peter_SSs

MrExcel MVP, Moderator

KrisGaczewski

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock