Look for the highest number

[Rye]

Hi Guys, is there a way to get the highest number automatically? please see the table below. Thank you for always helping us.

Meters	Score	High score
0	0	21
2	20
3	1
8	2
9	5
10	10
11	2
20	3
Is there a way to get the highest value automatically in this equation?
* My goal is to look for the highest score within 1 meter
* in this example, the highest score is 21
* Please note that, only the highest score will be recorded.

 

[Georgiboy]

Was playing with this and got to the answer but with helper columns etc..

I have run out of time for now but thought i would share where i got to in case it helps others create a more dynamic all in one solution:

Book1
	A	B	C	D	E	F	G	H
1	Meters	Score	0
2	0	0	1		20	2		21
3	2	20	2		1	2
4	3	1	2		2	3
5	8	2	3		5	3
6	9	5	3		10	3
7	10	10	3		2	3
8	11	2	3
9	20	3	4
Sheet1

Cell Formulas
Range	Formula
E2:F7	E2	=LET(r,TAKE(FILTER(A2:C9,COUNTIF(C2:C9,C2:C9)>1),,-2),r)
H2	H2	=LET(r,E2#,MAX(SUMIF(INDEX(r,,2),INDEX(r,,2),INDEX(r,,1))))
C2:C9	C2	=IF(A2-1=A1,C1,C1+1)
Dynamic array formulas.

 

[Rye]

Was playing with this and got to the answer but with helper columns etc..

I have run out of time for now but thought i would share where i got to in case it helps others create a more dynamic all in one solution:

Book1
	A	B	C	D	E	F	G	H
1	Meters	Score	0
2	0	0	1		20	2		21
3	2	20	2		1	2
4	3	1	2		2	3
5	8	2	3		5	3
6	9	5	3		10	3
7	10	10	3		2	3
8	11	2	3
9	20	3	4
Sheet1

Cell Formulas
Range	Formula
E2:F7	E2	=LET(r,TAKE(FILTER(A2:C9,COUNTIF(C2:C9,C2:C9)>1),,-2),r)
H2	H2	=LET(r,E2#,MAX(SUMIF(INDEX(r,,2),INDEX(r,,2),INDEX(r,,1))))
C2:C9	C2	=IF(A2-1=A1,C1,C1+1)
Dynamic array formulas.

Hi @Georgiboy, this is actually a great start. Actually I will try to use this formula now. I will surely let you know the outcome. Thank you. Enjoy your weekend.

 

[Rye]

Hi @Georgiboy, this is actually a great start. Actually I will try to use this formula now. I will surely let you know the outcome. Thank you. Enjoy your weekend.

Good day @Georgiboy, I tried the code, but unfortunately got a different result as I can't use multiple helpers in my case. Also, A1 may be not possible for me to use because these data are in the middle of my table and A1 has info in it. I really appreciate your help, I will try to play around more with the formula you have given. Cheers

 

[Peter_SSs]

See if this does what you want.

23 05 20.xlsm
	A	B	C
1	Meters	Score	High Score
2	0	0	21
3	2	20
4	3	1
5	8	2
6	9	5
7	10	10
8	11	2
9	20	3
High Score

Cell Formulas
Range	Formula
C2	C2	=LET(r,A2:A9,s,SEQUENCE(MAX(r)-MIN(r)+1,,MIN(r)),MAX(SUMIFS(B2:B9,r,">="&s-1,r,"<="&s+1)))

 

[Peter_SSs]

Re-reading the question I am now unsure about the "within 1 meter" requirement. Here is another set of sample data.
My original formula is in C2 and gets the total of 34 because all 3 green cells are within 1 meter of 10.
However, perhaps you would count that as "within 2 meters"? If so, then the formula in C3 may be what you want?

23 05 20.xlsm
	A	B	C
1	Meters	Score	High Score
2	0	0	34
3	2	20	27
4	3	1
5	8	2
6	9	7
7	10	10
8	11	17
9	20	3
High Score (2)

Cell Formulas
Range	Formula
C2	C2	=LET(r,A2:A9,s,SEQUENCE(MAX(r)-MIN(r)+1,,MIN(r)),MAX(SUMIFS(B2:B9,r,">="&s-1,r,"<="&s+1)))
C3	C3	=LET(r,A2:A9,s,SEQUENCE(MAX(r)-MIN(r)+1,,MIN(r)),MAX(SUMIFS(B2:B9,r,">="&s-1,r,"<="&s)))

 

[Rye]

See if this does what you want.

23 05 20.xlsm
	A	B	C
1	Meters	Score	High Score
2	0	0	21
3	2	20
4	3	1
5	8	2
6	9	5
7	10	10
8	11	2
9	20	3
High Score

Cell Formulas
Range	Formula
C2	C2	=LET(r,A2:A9,s,SEQUENCE(MAX(r)-MIN(r)+1,,MIN(r)),MAX(SUMIFS(B2:B9,r,">="&s-1,r,"<="&s+1)))

@Peter_SSs this code is an excellent fit in my situation table. Thank you very much for your help. Enjoy the rest of your weekend!!!

 

[Rye]

Table 1.1
Meters	Score	High Score
0	0	21
2	20
3	1
8	2
9	5
10	10
11	2
20	3
Table 1.2
Meters	Score	High Score
0	0	21
2	20
4	1		C14 should be displaying 20 in this table.
8	2
10	5
12	10
14	2
20	3
** Sorry, I did not explain it clearly.
** To make it easier, If in A2:A9 doesn’t have equal to 1m distance (Table 1.2) , get the highest single score in B2:B9.

 

[Rye]

Book1
	A	B	C	D
1	Table 1.1
2	Meters	Score	High Score
3	0	0	21
4	2	20
5	3	1
6	8	2
7	9	5
8	10	10
9	11	2
10	20	3
11
12	Table 1.2
13	Meters	Score	High Score
14	0	0	21
15	2	20
16	4	1		C14 should be displaying 20 in this table.
17	8	2
18	10	5
19	12	10
20	14	2
21	20	3
22
23	** Sorry, I did not explain it clearly.
24	** To make it easier, If in A2:A9 doesn’t have equal to 1m distance (Table 1.2) , get the highest single score in B2:B9.
Sheet1

Cell Formulas
Range	Formula
C3,C14	C3	=LET(r,A3:A10,s,SEQUENCE(MAX(r)-MIN(r)+1,,MIN(r)),MAX(SUMIFS(B3:B10,r,">="&s-1,r,"<="&s+1)))

 

[Peter_SSs]

Thanks for now using XL2BB. For the future, when you do that, don't put your instructions in the worksheet, just write them separately in your post (like I have with this message)

Looks like I was over-complicating it in the first place. What about this instead?

23 05 21.xlsm
	A	B	C
1	Table 1.1
2	Meters	Score	High Score
3	0	0	21
4	2	20
5	3	1
6	8	2
7	9	5
8	10	10
9	11	2
10	20	3
11
12	Table 1.2
13	Meters	Score	High Score
14	0	0	20
15	2	20
16	4	1
17	8	2
18	10	5
19	12	10
20	14	2
21	20	3
Sheet2 (3)

Cell Formulas
Range	Formula
C3,C14	C3	=LET(r,A3:A10,MAX(SUMIFS(B3:B10,r,">="&r-1,r,"<="&r+1)))