-1

Presently we use the following formula and it works great:

=IF(COUNTIF(D4:AB4,">0")<3,C4,IF((((SUM(D4:AB4)-(COUNTIF(D4:AB4,">0")*36))/COUNTIF(D4:AB4,">0")))*0.9>20,20,(((SUM(D4:AB4)-(COUNTIF(D4:AB4,">0")*36))/COUNTIF(D4:AB4,">0")))*0.9))

What is says is you must golf at least 3 times before the formula will recalculate a new handicap otherwise you are stuck with (C4) what you started with and we only use 90% of your average to compute your handicap.

However after many weeks of play ones handicap hardly changes due to the vast amount of entries; to keep the handicap current we want it to to use the 5 most current scores and yet do all the rest the formula presently does.

Because people miss or are absent there will no postings in some weeks so then we want it skip the blanks and just use the posted weeks

Golf score handicap sheet

1 Answer 1

1

you could use the formula:

=MIN(IF(COUNTIF(D4:AB4,">0")<3,C4,IF(COUNTIF(D4:AB4,">0")<=5,SUM(IF(D4:AB4>0,D4:AB4-36))/COUNTIF(D4:AB4,">0"),SUM(IF(COLUMN(D4:AB4)=LARGE(IF(D4:AB4>0,COLUMN(D4:AB4)),ROW($1:$5)),D4:AB4-36)/5))*0.9),20)

This is an array formula and must be confirmed with ctrl+shift+enter!

This fomula simply can be copied down as you need it.

The order of actions:

  • If the value which is picked is higher than 20, then show just 20 (also if 21+ is at C4)
    • less than 3 entries:
      • pick the value from C4
    • more than or equal to 3 entries
      • less then or equal to 5 entries
        • subtract 36 from each value which is bigger 0
        • sum up everything
        • divide by the amount of numbers
      • more than 5 entries
        • if value is > 0 then show column number
        • get the 5 biggest column numbers out of that list
        • if the column number of the column is in that list: return the value subtracted by 36
        • sum up everything
        • divide by five
      • multiply by 0.9

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .