Find automatic range

[neam]

Hi everyone
I want to intelligently calculate the average of that range by selecting the station column wherever kilometers change.

Point	Station	Offset	Elevation	Cross Section Station
305	9.67	17.478	102.039m	1	10
316	9.80	-3.148	101.528m	1
317	9.81	-5.016	101.847m	1
306	9.87	13.996	102.240m	1
322	9.88	-8.795	102.262m	1
323	9.91	-10.798	102.156m	1
320	9.92	-5.409	102.796m	1
321	9.96	-6.64	102.909m	1
312	10.02	2.451	101.650m	1
311	10.05	4.907	101.974m	1
310	10.10	5.274	102.553m	1
307	10.12	10.049	102.283m	1
308	10.13	7.144	102.126m	1
309	10.14	6.308	102.410m	1
325	10.18	-16.117	102.011m	1
324	10.23	-13.882	102.200m	0	47
301	46.15	8.529	102.155m	1
302	46.20	11.863	102.114m	1
298	46.22	4.401	102.413m	1
297	46.23	3.906	101.869m	1
296	46.31	2.787	101.573m	1
299	46.31	5.195	102.666m	1
300	46.33	5.941	102.636m	1
292	46.59	-2.705	101.492m	1
291	46.61	-4.625	102.073m	1
290	46.68	-5.085	102.636m	1
289	46.75	-6.38	102.617m	1
303	46.87	16.48	102.016m	1
288	46.91	-7.312	102.106m	1
287	46.93	-8.603	102.044m	1
283	46.96	-14.173	102.307m	1
285	46.97	-11.047	101.835m	1
284	46.98	-13.418	101.878m	1
286	46.99	-9.779	102.491m	1
281	46.99	-15.712	101.918m	1
282	46.99	-14.822	102.368m	1
280	47.14	-21.221	101.914m	1
279	47.15	-26.622	101.843m	1
304	48.62	18.268	102.049m	0	141

 

[Peter_SSs]

As long as the absolute value of the difference between the first and second numbers, the second and third numbers, etc., is between zero and two, the average of the numbers in this range is calculated.

This would also have been an incredible guess from the information in post #1

Would this be any use?

25 01 21.xlsm
	A	B	C
1	Station	Formula
2	9.666	10	10
3	9.796
4	9.809
5	9.866
6	9.882
7	9.907
8	9.916
9	9.955
10	10.024
11	10.046
12	10.095
13	10.117
14	10.128
15	10.142
16	10.178
17	10.225	47
18	46.153		47
19	46.204
20	46.221
21	46.225
22	46.307
23	46.309
24	46.325
25	46.59
26	46.612
27	46.681
28	46.753
29	46.87
30	46.914
31	46.929
32	46.955
33	46.966
34	46.976
35	46.988
36	46.99
37	46.993
38	47.139
39	47.149
40	48.624	141
41	140.542		141
Average

Cell Formulas
Range	Formula
C2:C41	C2	=IF(ABS(A2-N(A1))>2,ROUND(AVERAGE(A2:INDEX(A:A,ROW()+MATCH(TRUE,ABS(A2:A100-A3:A101)>2,0)-1)),0),"")

 

[neam]

CivilReport1.xls
	A	B	C
1	Station	Formula
2	9.666	10	#VALUE!
3	9.796
4	9.809
5	9.866
6	9.882
7	9.907
8	9.916
9	9.955
10	10.024
11	10.046
12	10.095
13	10.117
14	10.128
15	10.142
16	10.178
17	10.225	47
18	46.153		#VALUE!
19	46.204
20	46.221
21	46.225
22	46.307
23	46.309
24	46.325
25	46.59
26	46.612
27	46.681
28	46.753
29	46.87
30	46.914
31	46.929
32	46.955
33	46.966
34	46.976
35	46.988
36	46.99
37	46.993
38	47.139
39	47.149
40	48.624	141
41	140.542		#VALUE!
Sheet1

Cell Formulas
Range	Formula
C2:C41	C2	=IF(ABS(A2-N(A1))>2,ROUND(AVERAGE(A2:INDEX(A:A,ROW()+MATCH(TRUE,ABS(A2:A100-A3:A101)>2,0)-1)),0),"")

 

[neam]

Why am I not getting an answer?

 

[neam]

This would also have been an incredible guess from the information in post #1

Would this be any use?

25 01 21.xlsm
	A	B	C
1	Station	Formula
2	9.666	10	10
3	9.796
4	9.809
5	9.866
6	9.882
7	9.907
8	9.916
9	9.955
10	10.024
11	10.046
12	10.095
13	10.117
14	10.128
15	10.142
16	10.178
17	10.225	47
18	46.153		47
19	46.204
20	46.221
21	46.225
22	46.307
23	46.309
24	46.325
25	46.59
26	46.612
27	46.681
28	46.753
29	46.87
30	46.914
31	46.929
32	46.955
33	46.966
34	46.976
35	46.988
36	46.99
37	46.993
38	47.139
39	47.149
40	48.624	141
41	140.542		141
Average

Cell Formulas
Range	Formula
C2:C41	C2	=IF(ABS(A2-N(A1))>2,ROUND(AVERAGE(A2:INDEX(A:A,ROW()+MATCH(TRUE,ABS(A2:A100-A3:A101)>2,0)-1)),0),"")

Why am I not getting an answer?

 

[Peter_SSs]

Why am I not getting an answer?

You only need to ask once.

Try this.
Select cell C2
Press F2
Press Ctrl+Shift+Enter together. Excel will enclose the formula with { }
Copy the formula down again

 

[neam]

You only need to ask once.

Try this.
Select cell C2
Press F2
Press Ctrl+Shift+Enter together. Excel will enclose the formula with { }
Copy the formula down again

you are great

 

[Peter_SSs]

Glad it worked for you.

For the future, you need to be much clearer in telling us what you have and what you are trying to do. We cannot read your mind and we know nothing about what you have or what you are trying to do other than what you tell us and/or show us. You also need to be patient - remember that you are getting help for free.

 

[neam]

Glad it worked for you.

For the future, you need to be much clearer in telling us what you have and what you are trying to do. We cannot read your mind and we know nothing about what you have or what you are trying to do other than what you tell us and/or show us. You also need to be patient - remember that you are getting help for free.

I am very grateful to you for taking the time to solve my problem.
I hope you will forgive me for rushing this and also for my poor English.

 

[Peter_SSs]

Cheers. Thanks for your message.