@marvo

By using GoalSeek you'll discover that the value in BI2 must be 0.018667917. You need to be accurate to the 9th decimal.

Your sequence that generates an 82 rows X 1,000 columns array starting at 0.1% with steps of 0.1% is way to inaccurate. You'll need 100,000 columns starting at 0.0001% and steps of the same magnitude and divide by 1,000,000, to come reasonably close. Then, the totals of row 87 both add up to 9.950000 and 9.949511, both rounding to 10 with zero decimals. Close enough?

And I would also like to change the part where you create a sequence of 82 numbers 1 and use the SEQUENCE function rather then "TRANSPOSE(ROW(D4:D85)^0)".

The formula then becomes like this:

=LET(
    x, IF(
        (A4:A85 = 1) * (BB4:BB85 > 3) *
            (AQ4:AQ85 <= BG3),
        D4:D85
    ) * SEQUENCE(, 100000, 0.0001%, 0.0001%),
    XMATCH(
        BH87,
        MMULT(
            SEQUENCE(, ROWS(D4:D85), 1, 0),
            IF(x > 1, 1, x)
        ),
        -1
    ) / 1000000
)

Thank you so much, I was beginning to think it was an impossible task. 

@marvo 

Hi again!

Couldn't resist having another look at your schedule as I thought the approach taken was overly complicated when I realised that all you wanted to do is allocate the total 9.95 handicap reduction of the top three players, as a handicap increase to all others who had 35 points or less, based on their handicaps. Then you can simply calculate a player's increase as:

handicap / sum of all qualifying handicaps * 9.95

The formula in BI4 can then be fairly straight forward, like this:

=LET(
hcp,IF((A4:A85=1)*(BB4:BB85>3)*(AQ4:AQ85<=BG3),D4:D85,""),
tot,SUM(hcp),
tar,BH87,
IFERROR(hcp/tot*tar,0)
)

No need to create a huge array of 82 X 100,000 elements to find a correction factor.

Then I also thought that you could simplify the formulas in columns BA and BD. Attaching your file with all the changes. I believe you will notice a substantial improvement of its performance, i.e. calculation speed.

First off, thank you so much for taking an interest, I'm always looking at ways to improve this workbook. I'm no expert at Excel so quite a lot of my formula's are pretty basic stuff, and as you say, probably "overly complicated".

However, the working out of cuts and increases are a little more complicated than you have stated. 
The figure of "35 or less" is variable, it's actually 6 less than the winning (highest) score. So if the highest score was 32, increases would only be shared by those with 25 or less. The reason is, our rules state that if any player is within 6 points of the winner, they cannot receive any increase in handicap. So its actually 35pts or 6pts less than the highest score, whichever is the lowest figure.

The other stipulation which didn't come into affect in this particular competition is that if a player scores more than 36 and doesn't finish in the first three places, they can still receive a cut as 36 points is the benchmark in golf for playing to your handicap. The rule is 1 shot cut for every shot over 36 less 1 shot for how many players scored more than 36. So as an example, if the winner scored 44, second was 43, third was 42, 4th was 41, and 5th was 37, the person finishing 4th would be cut 5 shots, less 4 shots for the other 4 players that scored over 36, so in the end, a 1 shot cut.
Just so you understand the reasoning behind this, sometimes you have perfect conditions and an easy course so several people score over 36pts. In those circumstances players not finishing in the first three would not receive extra cuts. However if you had the first 4 scoring 45,44,43 & 42 and the next player scored 30, then those 4 players all scored exceptional highly above their handicap and against the rest of the field so in these circumstances, the player finishing 4th would be cut 3 shots (6 above 36 less 3 more players above 36). This is all worked out in column BG.

Having said all that, your improvements about balancing the difference between cuts and increases should still work, irrespective of the above.

Thanks once again for taking an interest, it is much appreciated. I will get on now with trying to add your improvements to the actual workbook. A new one is created (copied) after every competition.

So I had to change your formula so that if there was also an increase in BH that didn't involve those finishing 1st, 2nd or 3rd, it would stop that player getting an increase. As we established I have a pretty basic level of skill at this so the only way I could do it was to add a helper column (BC) which indicates all those players that have had their handicaps cut. Then instead of looking at BB>3 in your formula, instead look at BC>1

. This works but I'm sure I've gone the long way round in getting what I require. I did think instead of looking at BB, I could just look at BI and say any player above zero but that didn't work.

Been unable to copy this over to my Master sheet, keep getting a spill error and I don't know why.

@marvo 

You need to empty enough cells below the formula so that the dynamic array can spilled down in one go.

I've twigged that now but struggling. Works fine on the worksheet posted on here but can't get it to work on the Master sheet. I keep trying. Problem with the Master sheet is its empty as its ready for the next competition. 

What did you think about my solution to resolve the situation if someone other than the first three received a cut?

Ah, solved one part. No longer need the helper column. Changed your formula to

=LET(hcp,IF((A4:A85=1)*(BH4:BH85=0)*(AQ4:AQ85<=BG3),D4:D85,""),tot,SUM(hcp),tar,BH87,IFERROR(hcp/tot*tar,0))

That takes care of ALL players with increases, not just those who finish in the top three.

Getting there.

Okay, I think I've done it now. The attached file is the Master sheet ready for the next competition and using your formula. I don't know why BI is showing zero when the person isn't playing but that's not a real problem.

Sadly, I couldn't use your suggestions for BA and BD. 

For BA, it doesn't differentiate when there are no scores entered so everybody scheduled to play is 1st. As it is in the workbook, it does it alphabetically until scores are entered.

For BD, it returns zero points if somebody is not in the first 12 places. I need that to show nothing at all in that scenario for when the column is copied over to other workbooks.

You solved my problem though and in a much better way than with

the percentage increase I was using. I'm very happy with the result. Thank you so much for taking an interest.

@marvo 

Fair enough. I didn't consider blanking out the rows for those who didn't play as I'm not a fan of mixing numbers and texts in one column. But if you would prefer it, extend the LET function a bit.

=LET(
    playing, A4:A87 = 1,
    hcp, IF(
        playing * (BH4:BH87 = 0) *
            (AQ4:AQ87 <= BG3),
        D4:D87,
        ""
    ),
    tot, SUM(hcp),
    tar, BH89,
    incr, IFERROR(hcp / tot * tar, 0),
    MAP(
        incr,
        playing,
        LAMBDA(a, b, IF(b, a, ""))
    )
)

Add a variable 'playing' based on the values in A. Replace that reference in the 'hcp' variable with 'playing'. Then, name what used to be the last step (with IFERROR in it) 'incr' and add a new final calculation using MAP and a LAMBDA function. It compares both arrays 'incr' (a) and 'playing' (b) on a row by row basis. If the value in 'playing' is TRUE, the return the corresponding value from 'incr' otherwise return a blank.

See attached.

Sadly I cant use that, my version of Microsoft Office LTSC Professional Plus 2021 doesn't have those functions. Like I said, its not a problem though.

I did wonder, in your earlier LET formula, it did start with an IF statement

IF((A4:A87=1)*(BH4:BH87=0)*(AQ4:AQ87<=BG3),D4:D87,""

I would have thought would have returned a blank cell anyway when A4:A87 wasn't a 1?

Thanks anyway, I've enjoyed this episode, always nice to learn and at the end of the day you solved my initial problem with a far better method than I could so for that I am truly grateful. The workbook is noticeably quicker now too.

@marvo 

Wait a minute!!

your test file doesn't have any real scores, so the cut-off points become -6 and that is not very meaningful.

The original formula produced all zeros because of the IFERROR(------, 0).

Change it to IFERROR(------, "") and it shall work as soon as soon as you enter real scores in the Master. To test it, just enter e.g. 35 in BG3 and you'll see that only participating players outside the top 3 get adjusted. Non players are left blank.

I tried that but it produced VALUE errors all over the place. I think I prefer to leave it as it is for now.

Off to play golf now, you have a good day and thank you.