Golf Scorecard Automation

wayne0881

Board Regular
Joined
Nov 2, 2009
Messages
93
Hi All,

I'm working on a little fun project - but have come to a sticking point.

i'll give an example with a small data set:

in B11-B15 I have the name of a golfer in C11-T15 I have where I want the automation to happen (scores over 18 holes)
in C2-H2 I have a table of data (C2 is the min score on a par 3 for example 1, D2 is the maximum score on a par 3 for example 4 (E2-F2) will be par 4, min 3 max 5 (G2-H2) will be scores for par 5's, min 3 max 6)
in C9-T9 I have the Par of each hole (3-5)

At the moment I just have a manually typed Randbetween function

Example:

If hole one was a par 5 i'd have in C11 (for golfer 1) =RANDBETWEEN($G$2,$H$2) and just copy that down.

but the problem I have is when I change the pars for the holes in row C9-T9 i'll have to manually change the data in C11-T11 to match the par.

I'd ideally like the spreadsheet to look at the par of the hole C9 and then return the correct random number between the min and max values in C2-H2

So if hole 1 was a par 3 it would return a random number between C2-D2, for a par 4 E2-F2 etc...

Hope this makes sense - it's just a bit of fun and just can't put it down until I can work it out :)))

I've looked at index,match,randbetween, vlookups all on Youtube and am close but just can't quite get it, so will make do with a manual function for now!

Thanks in advance for any input.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
It would be so much easier if you either used XL2BB (see my tag) to post a sample of your data
OR better still upload to a hosting site, DropBox for instance, and then post the link back here.
 

wayne0881

Board Regular
Joined
Nov 2, 2009
Messages
93
PGA Leaderboard.xlsx
ABCDEFGHIJKLMNOPQRST
1Par 3Par3Par 4Par 4Par 5Par 5
21-352.521.4843536
336-753.521.48535.837
476-2004.521.4863638
5
6
7
8Hole123456789101112131415161718
9Par544345434445445345
10
111Dustin Johnson545356433356456443
122Jon Rahm453246443343354236
133Justin Thomas544346523544356455
144Collin Morikawa555436525345445334
155Xander Schauffele453354423555535336
166Bryson DeChambeau544334325444356336
Scores
Cell Formulas
RangeFormula
B2:B4B2=D2-C2
C11:C16,H11:H16,N11:N16,Q11:Q16,T11:T16C11=RANDBETWEEN($G$2,$H$2)
D11:E16,G11:G16,I11:I16,K11:M16,O11:P16,S11:S16D11=RANDBETWEEN($E$2,$F$2)
F11:F16,J11:J16,R11:R16F11=RAND()*$B$2+$C$2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
R11:R45Cell Value=1textNO
J11:J45Cell Value=1textNO
F11:F210Cell Value=1textNO
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Maybe this in C11
Excel Formula:
=IF(C$9=5,RANDBETWEEN($G$2,$H$4),IF(C$9=4,RANDBETWEEN($E$2,$F$4),RANDBETWEEN($C$2,$D$4)))
Then drag down to C16 and across to T16
 
Solution

wayne0881

Board Regular
Joined
Nov 2, 2009
Messages
93

ADVERTISEMENT

Maybe this in C11
Excel Formula:
=IF(C$9=5,RANDBETWEEN($G$2,$H$4),IF(C$9=4,RANDBETWEEN($E$2,$F$4),RANDBETWEEN($C$2,$D$4)))
Then drag down to C16 and across to T16
Looking good - that seems to have done it :)
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Ok...fingers crossed... (y) (y)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,557
Office Version
  1. 365
Platform
  1. Windows
I think this does the same job.

Excel Formula:
=RANDBETWEEN(INDEX($C$2:$H$4,1,C$9*2-5),INDEX($C$2:$H$4,3,C$9*2-4))
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,218
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top