I need a macro to select range of scores, choose the best scores and apply a formula

I'd much appreciate some help (as I've no idea what I'm doing) to create a macro that will select the latest 20 scores and differentials from a range, then choose the 10 lowest differentials and apply the following formula to them:

Average 10 lowest differentials, multiply result by 113, then divide that result by 115, then multiply that result by 0.96. The final result will be the projected handicap index and will be activated by a button.

The macro would update every time a new score/differential was added to the top of the range.

Hello,

Try this formula:

Formula:

Please Login or Register  to view this content.

This is an array formula so it will have to be entered with ctrl+shift+enter
http://www.excel-easy.com/functions/array-formulas.html
http://chandoo.org/wp/2010/06/04/ave...-top-5-values/

Thanks

I've tried using this formula, however, the same issue occurs when I insert a new row at the C4 position - The range then changes to $C$1 $C$24 which means more than 20 scores are being used and can make the outcome incorrect.

=AVERAGE(SMALL($C$1:$C$23,{1,2,3,4,5,6,7,8,9,10}))*113/115*0.96

Hi,

I am sorry for the late reply; I have been super busy

Use this formula

Formula:

Please Login or Register  to view this content.

where the new section added is:

Formula:

Please Login or Register  to view this content.

This last formula will return a range 1 row under the header (C3); no shift in the columns; the height is 20 rows; by 1 column: OFFSE($C$3,1,0,20,1)

Hope it helps

That's fantastic, works like a charm, thank you very much for that

You are welcome.