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]

I know nothing about the logic of this and @Fluff expressed some misgivings about how the average was calculated.
The OP was adamant it was as per the rules but you will need to make your own determination.

Here is final posted by Fluff in #8 with the modifications of #14 & #18.
It does not contain any of the newer functions so should be fully compatible with the versions you mentioned being from 2016 on.

20240629 Bowling Club Average Calc piccolo133.xlsx
	A	B	C	D	E	F	G	H
1	Week	Game 1	Game 2	Game 3	Series		Average	Handicap
2	1	175	187	200	562		187	2
3	2	163	205	197	565
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17	16
18	17
19	18
20	19
21	20
22	21
23	22
24	23
25	24
26				Total Pins	1127
Data

Cell Formulas
Range	Formula
G2	G2	=ROUNDDOWN(AVERAGE(B2:D25),0)
H2	H2	=MAX(ROUNDDOWN((190-G2)*0.9,0),0)
E2:E3	E2	=SUM(B2:D2)
E26	E26	=SUM(E2:E25)

 

[piccolo133]

I know nothing about the logic of this and @Fluff expressed some misgivings about how the average was calculated.
The OP was adamant it was as per the rules but you will need to make your own determination.

Here is final posted by Fluff in #8 with the modifications of #14 & #18.
It does not contain any of the newer functions so should be fully compatible with the versions you mentioned being from 2016 on.

20240629 Bowling Club Average Calc piccolo133.xlsx
	A	B	C	D	E	F	G	H
1	Week	Game 1	Game 2	Game 3	Series		Average	Handicap
2	1	175	187	200	562		187	2
3	2	163	205	197	565
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17	16
18	17
19	18
20	19
21	20
22	21
23	22
24	23
25	24
26				Total Pins	1127
Data

Cell Formulas
Range	Formula
G2	G2	=ROUNDDOWN(AVERAGE(B2:D25),0)
H2	H2	=MAX(ROUNDDOWN((190-G2)*0.9,0),0)
E2:E3	E2	=SUM(B2:D2)
E26	E26	=SUM(E2:E25)

Unfortunately I don't need this sheet, I need the one posted by mackc557, 3rd reply of the thread, follow this Link

 

[Alex Blakenburg]

I avoid volatile functions such and Indirect and Address and I suspect that Fluff's version will achieve the same result.
However in answer to your question, there are no functions in that solution that use new features and it will be compatilble with 2016+.
You will need to set up the 3 named ranges in the cells A3,B3 & C3 for it to work.

 

[piccolo133]

I've set up the ranges, I've replaced comas to semicolons in formulas because comas give me error, but still the columns average and handicap only output 0, no matter what, I have no idea what's wrong with it

 

[Alex Blakenburg]

Either post your worksheet using xl2bb or put a copy of the workbook on a sharing platform ie Dropbox, Google drive etc so we can take a look.

 

[piccolo133]

here's google drive link: workbook
thank you for your help, i'm really lost

 

[Sektor]

Because you have circular references. When you open this workbook, Excel tells you just this:

To see all your circular references, go here:

 

[Alex Blakenburg]

As @Sektor has pointed out that formula produces a circular reference. Can you tell us what it is supposed to be trying to do and give some examples of expected results and how they are calculated ?

 

[piccolo133]

Yes i see the circular reference, the thing is that i simply copy pasted the bb2xl you can find in this post: Link
and i tried to srun it in excel 365
So i didn't write the formulas, i can't seem to find where's the circular reference or how to fix it, that's why am asking if somebody with more knowledge than me can fix it
Basically the sheet does this: it should calculate the average of each the player based on the last x games he played (where x is in cell C2), then with this average it should calculate a Handicap for each player using this formula: (A2-Average)*B2