Excel, find the maximum value and the neighboring N members before and after
The column below contains numeric values only:
A |
|
1 |
13 |
2 |
21 |
3 |
46 |
4 |
21 |
5 |
49 |
6 |
9 |
7 |
34 |
8 |
23 |
9 |
6 |
10 |
1 |
11 |
37 |
12 |
49 |
13 |
42 |
14 |
40 |
15 |
15 |
16 |
31 |
17 |
17 |
18 |
1147 |
19 |
18 |
20 |
30 |
21 |
22 |
22 |
4 |
23 |
25 |
24 |
19 |
25 |
13 |
26 |
27 |
27 |
38 |
28 |
30 |
29 |
16 |
30 |
12 |
31 |
23 |
32 |
3 |
33 |
23 |
34 |
19 |
35 |
14 |
36 |
46 |
37 |
23 |
38 |
37 |
39 |
38 |
40 |
28 |
We need to find out the maximum value and the 10 neighboring members both before and after it. Rember to perform out of bounds check as it is possible that the actual number of eligible values is less than 10.
A |
|
1 |
23 |
2 |
6 |
3 |
1 |
4 |
37 |
5 |
49 |
6 |
42 |
7 |
40 |
8 |
15 |
9 |
31 |
10 |
17 |
11 |
1147 |
12 |
18 |
13 |
30 |
14 |
22 |
15 |
4 |
16 |
25 |
17 |
19 |
18 |
13 |
19 |
27 |
20 |
38 |
21 |
30 |
Use SPL XLL to enter the formula below:
=spl("=p=(d=?).pmax(),d.calc(p,~[-10:10])",A1:A40)
pmax()function gets position of the maximum value. calc() function performs the computation according to the specified positions; ~ represents the current member, and [] gets members according to the interval specified by the relative positions, which automatically prevents array index out of bounds.
Source:https://old.reddit.com/r/excel/comments/1d41iix/find_highest_value_with_15_rows_before_and_after/
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
