Looking for Assistance with Formula (Top 30 Values Closest to ZERO) Please :)

[Iccreamann21]

1	-0.14 <tbody> </tbody>	top 30 values in column B1-B32 that is closest to 0
2	-0.04 <tbody> </tbody>	I want to project it as a formula in C1-C30
3	-0.02 <tbody> </tbody>
4	0.09 <tbody> </tbody>
5	0.23 <tbody> </tbody>
6	-0.12 <tbody> </tbody>
7	0.21 <tbody> </tbody>
8	0.2 <tbody> </tbody>
9	0.04 <tbody> </tbody>
10	0.07 <tbody> </tbody>
11	-0.03 <tbody> </tbody>
12	-0.07 <tbody> </tbody>
13	-0.02 <tbody> </tbody>
14	0.01 <tbody> </tbody>
15	0.02 <tbody> </tbody>
16	0.08 <tbody> </tbody>
17	-0.15 <tbody> </tbody>
18	0.17 <tbody> </tbody>
19	0.33 <tbody> </tbody>
20	-0.16 <tbody> </tbody>
21	0.02 <tbody> </tbody>
22	0.05 <tbody> </tbody>
23	0
24	-0.07 <tbody> </tbody>
25	0.04 <tbody> </tbody>
26	0.03 <tbody> </tbody>
27	0.06 <tbody> </tbody>
28	-0.02 <tbody> </tbody>
29	-0.05 <tbody> </tbody>
30	0.06 <tbody> </tbody>
31	0.01 <tbody> </tbody>
32	-0.03 <tbody> </tbody>

<tbody>
</tbody>

 

[tyija1995]

The first one is because that is the closest to 0 as it is equal to 0, then the last is because of multiplying by the SIGN of 0

 

[Eric W]

Let me suggest 2 other approaches. If you want a formula, try:

Book1
	B	C
1	-0.14	0
2	-0.04	0.01
3	-0.02	0.01
4	0.09	-0.02
5	0.23	-0.02
6	-0.12	0.02
7	0.21	0.02
8	0.2	-0.02
9	0.04	-0.03
10	0.07	0.03
11	-0.03	-0.03
12	-0.07	-0.04
13	-0.02	0.04
14	0.01	0.04
15	0.02	0.05
16	0.08	-0.05
17	-0.15	0.06
18	0.17	0.06
19	0.33	0.07
20	-0.16	-0.07
21	0.02	-0.07
22	0.05	0.08
23	0	0.09
24	-0.07	-0.12
25	0.04	-0.14
26	0.03	-0.15
27	0.06	-0.16
28	-0.02	0.17
29	-0.05	0.2
30	0.06	0.21
31	0.01	0.23
32	-0.03	0.33
Sheet12

Cell Formulas
Range	Formula
C1	{=INDEX(B:B,MOD(SMALL(MMULT(--(ABS(B$1:B$32)>TRANSPOSE(ABS(B$1:B$32))),ROW(B$1:B$32)^0)*100+ROW(B$1:B$32),ROWS($C$1:$C1)),100))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Second, put =ABS(B1) in C1. Copy down to C32. Then sort columns B:C by column C in ascending order. Column B should give you what you want. Of course, you can copy your range somewhere else to do this if you want to keep your original list as is.