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

[Iccreamann21]

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

 

[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.