Quick help at troubleshooting sheet compatibility towards excel 365

[piccolo133]

Hello i copied the same excel sheet that can be found in this mrexcel post: Link
The problem is that it has been created using an old excel version, probably 2016 or 2019 or 2021, i want to use it on excel 365, but still be backwards compatible for old excel versions, can anyone help me troubleshoot it?
The columns "average" and "handicap" only display 0 when i run it on excel 365 and i'm not so good at excel to understand why
thanks in advance for any help

 

[Alex Blakenburg]

Using your explanation of the calculations try this in B5

=LET(rGames,$D5:$Z5,AVERAGE($D5:INDEX(rGames,0,$C$2)))

And this is C6

=($A$2-B5)*$B$2

Note: Z is an arbitrary column reference can be anything that is bigger than you think you will ever need.

 

[piccolo133]

i remade it all from scratch: link
it's written in italian but you get the point
it now works the way i intend to, it doesn't work on google drive but if you download it it works correctly and averages the last 3 games.
i just need 1 more thing, is it possible to create cells from D2 to D100 where if i input a value it gets automatically written in the first empty cell of that same row? so that i don't have to scroll all the way right to input the next game when the number of games becomes high?

 

[Alex Blakenburg]

Since column D is one of your data columns, I am not sure that would work.
You could do something like. Use double click to move to the next blank cell in the row.

eg right click on the sheet tab name and View Code and copy in the below.
Then double click in a populated cell on the row you want to enter another game into.
You will need to save the workbook as Macro enabled (xlsm) if you want to keep the code though.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cells(Target.Row, Target.Column).End(xlToRight).Offset(, 1).Select
End Sub