Minimum value of the Top 10%

[x077695]

Hello Folks,
I need a formula to the display the minimum value after a calculation of the top 10% of a range of values.
Thanks!!

 

[dreid1011]

Perhaps this:

Test Sheet Summary.xlsx
	A	B
1	32	94
2	81
3	80
4	69
5	42
6	49
7	81
8	14
9	30
10	8
11	87
12	7
13	21
14	96
15	74
16	63
17	6
18	19
19	60
20	38
21	66
22	4
23	71
24	22
25	33
26	14
27	65
28	100
29	85
30	15
31	83
32	4
33	60
34	56
35	2
36	47
37	79
38	96
39	12
40	46
41	100
42	79
43	65
44	7
45	15
46	68
47	94
48	87
49	75
50	50
51	19
52	96
53	93
54	30
55	57
56	81
57	67
58	96
59	73
60	88
61	20
62	91
63	11
64	23
65	74
66	98
67	96
68	30
69	12
70	68
71	41
72	23
73	22
74	57
75	18
76	77
77	62
78	40
79	37
80	53
81	18
82	67
83	7
84	33
85	37
86	33
87	33
88	58
89	26
90	32
91	93
92	95
93	42
94	77
95	35
96	32
97	35
98	59
99	54
100	84
Summary

Cell Formulas
Range	Formula
B1	B1	=MIN(CHOOSEROWS(SORT(A1:A100,1,-1),SEQUENCE(10,,1,1)))

 

[dreid1011]

Sorry, slight adjustment to account for variable sized ranges:

=MIN(CHOOSEROWS(SORT(A:A,1,-1),SEQUENCE(COUNTA(A:A)/10,1,1,1)))

Although, I might suggest setting the range from row 2 to reasonably large enough to cover most, or all, sizes you might encounter if your data includes headers. ie: A2:1000 if your data won't ever be larger than 1000 rows.

 

[Rick Rothstein]

Maybe this formula (set the range to the rng variable)...

=LET(rng,A1:A100,MIN(TAKE(SORT(rng,,-1),0.1*COUNT(rng))))

 

[x077695]

Sorry, slight adjustment to account for variable sized ranges:

=MIN(CHOOSEROWS(SORT(A:A,1,-1),SEQUENCE(COUNTA(A:A)/10,1,1,1)))

Although, I might suggest setting the range from row 2 to reasonably large enough to cover most, or all, sizes you might encounter if your data includes headers. ie: A2:1000 if your data won't ever be larger than 1000 rows.

Work great. Thanks!

 

[dreid1011]

You're welcome, and happy to help.